グッドデザイン賞を受賞したウォーターサーバー >>

特定の文字がある行以外を削除するマクロを作りたいです。

データの範囲はA1からK50000
E列にS01、S02、E03と書いてあるセル以外の行を削除したいです。

E列にはS01~S50、E01~E100というような数値があります。

どうぞよろしくお願いします。

このQ&Aに関連する最新のQ&A

A 回答 (8件)

NO5です。


>やってみましたが、かなり時間がかかってしまいました。
画面更新を無効にする事でもかなり時間短縮できますが如何でしょうか。

Sub 行を削除()
Application.ScreenUpdating = False
For i = Cells(Rows.Count, "E").End(xlUp).Row To 1 Step -1
If Cells(i, "E") <> "S01" And _
Cells(i, "E") <> "S02" And _
Cells(i, "E") <> "E03" Then
Rows(i).Delete
End If
Next
Application.ScreenUpdating = True
End Sub
    • good
    • 3
この回答へのお礼

何度もありがとうございます。
1分以上かかってしまいましたが、できました。
本当にありがとうございます。

お礼日時:2011/02/11 03:06

補足で。



削除とマイナス、
これくらいならデータ発生元を修正依頼しても難しい話でないだろうが?。

削除は一度失敗するとリランが面倒。削除などせず別シートに必要なデータを作成し、シート名入れ替えれば結果は同じで原本も残るというもの。保守性はどうか?。

削除もFINDも処理遅いし、いつか改善になる可能性あり。

区分増えてもマクロ変えないようにしたいとかないのか。

この回答への補足

これから考えていきたいと思います。
貴重なご意見ありがとうございます。

補足日時:2011/02/11 03:12
    • good
    • 1

5000行じゃなく5万行のデータなんですね?


ならば、行の削除じゃやたら時間がかかると思います。
いったん配列に取り込んで必要なデータだけ残してあとは消すようにしてみました。

Sub test01()
  Dim myV, myW, myStr
  Dim i As Long, j As Long, n As Long, k As Long
  myStr = Split("S01、S02、E03", "、")
  myV = Range("A1:K50000").Value
  ReDim myW(1 To 50000, 1 To 11)
  For i = 1 To 50000
    For k = 0 To 2
      If myStr(k) = myV(i, 5) Then
        n = n + 1
        For j = 1 To 11
          myW(n, j) = myV(i, j)
        Next j
        Exit For
      End If
    Next k
  Next i
  Range("A1:K50000").ClearContents
  Range("A1:K" & n).Value = myW
End Sub
    • good
    • 0
この回答へのお礼

ありがとうございます。
If myStr(k) = myV(i, 5) Thenでエラーになってしまいました。
あと、 myStr = Split("S01、S02、E03", "、")の全角「、」がOSの関係上記載できません。

どうしたらいのでしょうか。

お礼日時:2011/02/11 01:49

一例です。


E列を元に規定の文字列(英数字は半角としています)以外の行を削除

Sub 行を削除()
For i = Cells(Rows.Count, "E").End(xlUp).Row To 1 Step -1
If Cells(i, "E") <> "S01" And _
Cells(i, "E") <> "S02" And _
Cells(i, "E") <> "E03" Then
Rows(i).Delete
End If
Next
End Sub
    • good
    • 0
この回答へのお礼

ありがとうございます。
やってみましたが、かなり時間がかかってしまいました。

お礼日時:2011/02/10 07:59

No.3です!



前回のコードで誤りがありました。

4行目の
If Cells(i, 5) <> "S01" And Cells(i, 5) <> "S05" And Cells(i, 5) <> "E03" Then

If Cells(i, 5) <> "S01" And Cells(i, 5) <> "S02" And Cells(i, 5) <> "E03" Then
に訂正してください。

何度も失礼しました。m(__)m
    • good
    • 0
この回答へのお礼

やってみたら全部削除されてしまいました。
やはり文字列の問題でしょうか。
前後の空白も一緒にしてみたんですが。。。


でも非常に参考になりました。
ありがとうございました。

お礼日時:2011/02/11 01:44

こんにちは!


一例です。
データは必ず5000行目まであるとして・・・

Sub test()
Dim i As Long
For i = 5000 To 1 Step -1
If Cells(i, 5) <> "S01" And Cells(i, 5) <> "S05" And Cells(i, 5) <> "E03" Then
Range(Cells(i, 1), Cells(i, 11)).Delete (xlUp)
End If
Next i
End Sub

こんな感じではどうでしょうか?
尚、データがなくても5000行目から操作していきますので、少し時間がかかると思います。m(__)m
    • good
    • 0

sub sample2()


 if application.counta("E2:E500") = 0 then exit sub
 range("L:L").insert
 range("L2:L5000").formula = "=(E2=""S01"")+(E2=""S02"")+(E2=""E03"")"
 range("L:L").autofilter field:=1, criteria1:=0
 range("A2:L5000").entirerow.delete shift:=xlshiftup
 activesheet.autofiltermode = false
 range("L:L").delete shift:=xlshifttoleft
end sub

など。
    • good
    • 0
この回答へのお礼

やってみたんですが。データが一つも削除されませんでした。
S01等の文字がうまく認識されてないのかな???

でも非常に参考になりました。
ありがとうございました。

お礼日時:2011/02/11 01:40

マクロを使うよりも関数を使って別のシートに抽出したものを表示させるのがよいでしょう。



シート1の2行目から下方にデータがあるとしてL2セルには次の式を入力します。

=IF(OR(E2="S01",E2="S02",E2="E03"),MAX(L$1:L1)+1,"")

その後にL2セルを選択して右クリックして「コピー」を選択します。
次いで数式バー上にはL2と表示されていますがL2:L50000と入力してから確定します。これでL2セルからL50000セルまでが選択状態になりますので右クリックして「貼り付け」を選択します。
これでL列にはS01,S02,E03と書かれた行にのみ上から番号が付けられます。

次にシート2に移ってA2セルには次の式を入力します。

=IF(COUNTIF(Sheet1!$L:$L,ROW(A1))=0,"",INDEX(Sheet1!$A:$K,MATCH(ROW(A1),Sheet1!$L:$L,0),COLUMN(A1)))

その後にA2セルを選択してから右クリックして「コピー」を選択します。
次いで数式バー上にはA2と表示されていますがA2:K50000と入力してから確定します。これでA2セルからK50000セルまでが選択状態になりますので右クリックして「貼り付け」を選択します。

お望みの表が表示されますね。

この回答への補足

早速回答ありがとうございます。

でも今回は、会計データベースからダウンロードしたデータに一連の処理
をして、別のデータを作るというマクロを作成しており、
この作業はその途中で発生するものです。
なので、すみませんがマクロの記載方法を教えていただけると大変助かります。

どうぞよろしくお願いします。

補足日時:2011/02/09 07:30
    • good
    • 0

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

QエクセルVBA 特定文字以外の行削除

こんにちは。

先日以下のURLで質問させて頂きました者です。
http://okwave.jp/qa/q8567085.html

そこで、教えて頂いたマクロは成功したのですが、
inputboxではない方法を知りたいです。

---前回頂きました回答を引用しております------------------------------
retu = "D"
word = InputBox(retu & "列に指定した文字が含まれていない行を削除します。" _
& vbCrLf & "検索する文字を入力してください。")
For i = Range("D" & "65536").End(xlUp).Row To 2 Step -1
If InStr(1, Range(retu & i).Value, word) = 0 Then
Rows(i).Delete
End If
Next i
------------------------------------------------------------------
この文の文字を入力せずに、
マクロの中に特定の文字を記入して、その文字列以外の行を削除したいです。

ご教授頂けたらと思います。

宜しくお願い致します。

こんにちは。

先日以下のURLで質問させて頂きました者です。
http://okwave.jp/qa/q8567085.html

そこで、教えて頂いたマクロは成功したのですが、
inputboxではない方法を知りたいです。

---前回頂きました回答を引用しております------------------------------
retu = "D"
word = InputBox(retu & "列に指定した文字が含まれていない行を削除します。" _
& vbCrLf & "検索する文字を入力してください。")
For i = Range("D" & "65536").End(xlUp).Row To 2 Step -1
If InStr(1, Range(retu & i).Value, word...続きを読む

Aベストアンサー

こんにちは!

ごく簡単に

Sub Sample1()
Dim i As Long
For i = Cells(Rows.Count, "D").End(xlUp).Row To 2 Step -1
If InStr(Cells(i, "D"), "検索したい文字") = 0 Then
Rows(i).Delete
End If
Next i
End Sub

こんな感じではどうでしょうか?

※ 「検索したい文字」そのものだけではなく、
「検索したい文字」が「含まれている」場合も適応されます。
「検索したい文字」そのものの場合は

>If InStr(Cells(i, "D"), "検索したい文字") = 0 Then
の行を
>If Cells(i, "D") <> "検索したい文字" Then
に変更してください。m(_ _)m

Qエクセルで特定の行を削除したいのですが。

エクセルで特定の行を一発で削除したいのですが、やり方がわかりません。
どなたか詳しい方お教えいただけませんでしょうか?

やりたいことは、B列に、特定の文字が有れば、その行全部を削除して上方向にシフトしていきたいのですが、マクロとかを使うのでしょうか?
宜しくお願いいたします。

Aベストアンサー

マクロを使う別の方法です。
XXXの部分を特定の文字に置きかえて実行してください。
また、「特定の文字があれば」というのが、その文字列を含む、というのでなくセルの値がその文字列ならば、というのであれば、LookAt:=xlPart の部分を LookAt:=xlWhole に書き換えてください。

Sub DelLines()
  Dim R As Range
  Do
    Set R = ActiveSheet.Range("B:B").Find(What:="XXX", LookAt:=xlPart)
    If R Is Nothing Then Exit Sub
    R.EntireRow.Delete
  Loop
End Sub

QExcel VBA A列が特定の値以外の場合、その行を削除

教えてください。

A列の値が特定の値以外の場合、
その行を行ごと削除する方法はありますでしょうか?
A列には、
1 タイトル
2 (ブランク)
3 a
4 a
5 b
6 b
7 c
8 c
9 d
10 d
~というふうに2000行辺りまで続きます

例えば、A列の3行目以降の値が b 以外の場合、
3・4・7・8・9・10行を行ごと削除する、ということです。

あまり説明が上手でなくて申し訳ありませんが、よろしくお願いします。

Aベストアンサー

ではVBAのサンプルです。コメントも入れておきました。

Sub test01()
With ActiveSheet 'アクティブなシートについて
x = .UsedRange.Cells(.UsedRange.Count).Row 'xに最終行を取得
For i = x To 3 Step -1 '最終行から3行目まで下から順に
If .Cells(i, 1) <> "b" Then .Rows(i).Delete 'A列が"b"でなかったら削除
Next '繰り返し
End With
End Sub

Q指定した文字があった場合、その行を削除するマクロが欲しいです

指定した文字があった場合、その行を削除するマクロが欲しいです
Sheet1(Sheet1以外は対象外)のB列に
XYZ
という文字があった場合、その行をすべて削除する
というマクロはどのように作ればいいでしょうか?
お時間ある方アドバイスいただければ幸いです。

Aベストアンサー

手抜きですがこんな感じでどうでしょう。
削除する行が多いなら画面更新を停止した方が良いでしょう。

Sub Sample()
 Sheets("Sheet1").Select
 Do While (True)
  Columns("B:B").Select
  Set mySelect = Selection.Find(What:="XYZ")
  If mySelect Is Nothing Then Exit Do
  Rows(mySelect.Row).Select
  Selection.Delete Shift:=xlUp
 Loop
End Sub

Qエクセル 指定した文字列を含む列を削除するには

人から貰ったエクセルの表を必要なデータを残して削除する作業をしています。
(例:セルK5やM5に現在在庫数が入っている列が不要なので削除)
行に関してはオートフィルタを使って削除していますが、
列に関しては行列を入替えて貼付→オートフィルタで削除→行列を入替えて貼付
で実現出来ていますが、かなり時間と手間がかかっています。

もう少し簡単に上記作業を実現したいのですが、どうしたらよいでしょうか?

Aベストアンサー

> 指定した文字列を含む列を削除するには

素直に解釈して回答します。Excel2002 以降限定ですが。

 1. 検索するセルの範囲を選択
 2. [Ctrl]+[F] で検索ダイアログを表示
 3. 検索する文字列を入力
 4. [全て検索] をクリック
 5. リストボックスに表示された結果を全て選択
   --> セルが選択されます
 6. セル上で右クリック [削除]
 7. 6. で表示されるダイアログで、削除対象を [列全体] に指定

でできませんか?

Qエクセルで、条件に一致した行を別のセルに抜き出す方法

エクセルで、指定した条件に一致するセルを含む行をすべて抜き出す方法が知りたいです。

たとえば、

<A列> <B列> <C列>
7/1 りんご 100円
7/2 ぶどう 200円
7/2 すいか 300円
7/3 みかん 100円

このような表があって、100円を含む行をそのままの形で、
別のセル(同じシート内)に抜き出したいのですが。

7/1 りんご 100円
7/3 みかん 100円

抽出するだけならオートフィルターでもできますが、
抽出結果を自動的に、別の場所に、常に表示させておきたいのです。

初歩的な質問だと思いますが、検索しても分からなかったので、よろしくお願いします。

Aベストアンサー

同じ質問が結構よく出てますが、そんなに初歩的でもありません
別シートのA1セルに「100円」と入力し、そのシートの任意のセルに以下の式を貼り付けて下さい。後は、下方向、右方向にコピー。
日付のセル書式は「日付」形式に再設定してください

=IF(COUNTIF(Sheet1!$C:$C,$A$1)>=ROW(A1),INDEX(Sheet1!A:A,LARGE(INDEX((Sheet1!$C$1:$C$500=$A$1)*ROW(Sheet1!$C$1:$C$500),),COUNTIF(Sheet1!$C:$C,$A$1)-ROW(A1)+1)),"")

データ範囲は500行までとしていますが、必要に応じて変更して下さい

QExel VBA 別ブックから該当データを検索し、必要なデータを取得する方法について

部品表というブックがあります
A列に商品名、B列に商品番号が入力してあります。C列のコードは未入力です。
A列     B列     C列      
商品名  商品番号  コード
モータ  U-1325-L  
ホルダ  R-134256

また、コード一覧表という別のブックには、A列に商品番号と、B列にコードが、何千件も入力されています。

やりたいことは
部品表のC列のコード欄に、コード一覧表ブックから商品番号と一致するコードを貼り付けしたいのです。

部品表は、何百種類もありますので、関数ではなく、マクロで処理を希望します。

自分では、部品表の商品番号をコピーして、コード一覧表で検索し、検索結果の右隣のセル(B列のコード)の値を部品表のC列に貼り付ければよいかと思い、書いてみたんですが…

Sub 別ブックから貼り付ける()
  Dim 検索する As Long
Windows("部品表.xls").Activate
検索する = cells(i,2).Value
Windows("コード一覧表.xls").Activate
ActiveWindow.SmallScroll Down:=-3
Selection.AutoFilter Field:=3, Criteria1:="=検索する", Operator:= xlAnd

と、してみたものの、検索しても、その検索結果の隣のセルのコードをどうやって取得すればいいのかが、わかりませんでした。

基本事項は本で学びましたが、呪文のようなコードはよく理解できません。懸命にネットで検索して、訳して理解する努力をしてはいますが。

どうぞよろしくお願いします。

部品表というブックがあります
A列に商品名、B列に商品番号が入力してあります。C列のコードは未入力です。
A列     B列     C列      
商品名  商品番号  コード
モータ  U-1325-L  
ホルダ  R-134256

また、コード一覧表という別のブックには、A列に商品番号と、B列にコードが、何千件も入力されています。

やりたいことは
部品表のC列のコード欄に、コード一覧表ブックから商品番号と一致するコードを貼り付けしたいのです。

部品表は、何百種類もありますので、関数...続きを読む

Aベストアンサー

こんにちは。
とりあえず実用性も踏まえました。
メインの動作はワークシート関数のVLOOKUPをVBA上で使用していますので理解はしやすいかと思います。
また、質問文から察するに「部品表.xls」と「コード一覧表.xls」の両方を開いて処理されていますが「コード一覧表.xls」はプログラム内で開いて閉じているので実行するときは「コード一覧表.xls」は閉じて置いてください。
Option Explicit
Sub Sample()
 Application.ScreenUpdating = False
 Dim I As Long
 Dim xlBook
 Set xlBook = Workbooks.Open("C:\★★\コード一覧表.xls") '★要変更★
 I = 2
 Do While Range("A" & I).Value <> ""
  ThisWorkbook.Worksheets("Sheet1").Range("C" & I).Value = Application.VLookup(ThisWorkbook.Worksheets("Sheet1").Range("B" & I).Value, xlBook.Worksheets("Sheet1").Range("A2:B65535"), 2, 0)
  I = I + 1
 Loop
 xlBook.Close
 Application.ScreenUpdating = True
 MsgBox ("完了")
End Sub

こんにちは。
とりあえず実用性も踏まえました。
メインの動作はワークシート関数のVLOOKUPをVBA上で使用していますので理解はしやすいかと思います。
また、質問文から察するに「部品表.xls」と「コード一覧表.xls」の両方を開いて処理されていますが「コード一覧表.xls」はプログラム内で開いて閉じているので実行するときは「コード一覧表.xls」は閉じて置いてください。
Option Explicit
Sub Sample()
 Application.ScreenUpdating = False
 Dim I As Long
 Dim xlBook
 Set xlBook = Workbooks....続きを読む

Qエクセルマクロ 特定の文字列を含む行を削除

マクロについて教えてください。
以下のなかから、りんごを含んでいる行のみ削除したいのです。
できれば、inputboxで削除したい文字列を入力するようにしたいです。
1|りんご|30
2|みかん|50
3|りんご|30
4|すいか|30
5|みかん|50
いろいろ調べましたがわかりません。。お願いします。

Aベストアンサー

>りんごを含んでいる行のみ削除
これは特定の列に削除文字列があれば削除するのですか? それとも列を限定せず1行のどこかに文字列があれば削除するのですか?

以下のサンプルはマクロ2行目で指定した列に指示した文字列がある行を削除していますが、マクロ中コメントアウトしている9行目を8行目のIf文と入れ替えると、1行のどこかに指定文字列があれば削除します。

Sub Macro1()
Const col As String = "A" '文字列が入力されている列
Dim idx As Long
Dim keyWord
keyWord = Application.InputBox("削除対象の文字列は?", Type:=2)
If TypeName(keyWord) <> "Boolean" And Len(keyWord) > 0 Then
  For idx = Cells(65536, col).End(xlUp).Row To 1 Step -1
    If InStr(Cells(idx, col).Value, keyWord) > 0 Then
'    If Application.CountIf(Rows(idx), "*" & keyWord & "*") > 0 Then
      Rows(idx).Delete
    End If
  Next idx
End If
End Sub

>りんごを含んでいる行のみ削除
これは特定の列に削除文字列があれば削除するのですか? それとも列を限定せず1行のどこかに文字列があれば削除するのですか?

以下のサンプルはマクロ2行目で指定した列に指示した文字列がある行を削除していますが、マクロ中コメントアウトしている9行目を8行目のIf文と入れ替えると、1行のどこかに指定文字列があれば削除します。

Sub Macro1()
Const col As String = "A" '文字列が入力されている列
Dim idx As Long
Dim keyWord
keyWord = Application.Inp...続きを読む

Qある範囲のセルから任意の値を検索して、その隣のセルの値を取得するという関数はありますか?

Excelの関数について質問します。
ある範囲のせるを検索して、その隣のセルの値を取得するという関数を探しています。
なければユーザー定義で作りたいと思っています。
VLOOKUP関数では一番左端が検索されますが、
それをある範囲まで拡張して、
その右隣の値を取得できるようにしたいのです。
どうかお知恵をお貸しください。

Aベストアンサー

●X1セルの値を範囲A1:F200の中から探して、その右隣のセルの値を返す

 =OFFSET(A1,SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1))-1,SUMPRODUCT(COLUMN(A1:F200)*(A1:F200=X1)))

※最初のA1はワークシートの左上隅を示すものなので、検索範囲に関わらずA1固定
※SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1)) ⇒ A1:F200で値がX1と一致するセルの行番号

>その「ある範囲」の中には検索したい値が入っているセルは1つしかありません。
というのが前提です。複数のセルがHITすると関係ないセルの値が返るので、
場合によっては、IFをかぶせてCOUNTIFで確認した方が良いかもしれません。
 ex. =IF(COUNTIF(A1:F200,X1)=1,【上記数式】,"えらー")

ちなみに、VBAでやるならこんな感じになるかと。

動作の概要
 【検査範囲】から【検査値】を探し、
 最初にHITしたセルについて、右隣のセルの値を返す。
 ex. =Sample(X1,A1:F200)

'--------------------------↓ココカラ↓--------------------------
Function Sample(ByVal 検査値 As Variant,ByVal 検査範囲 As Range)
 For Each セル In 検査範囲
  If セル = 検査値 Then Exit For
 Next セル
 Sample = セル.Offset(0, 1)
End Function
'--------------------------↑ココマデ↑--------------------------

いずれもExcel2003で動作確認済。
以上ご参考まで。

●X1セルの値を範囲A1:F200の中から探して、その右隣のセルの値を返す

 =OFFSET(A1,SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1))-1,SUMPRODUCT(COLUMN(A1:F200)*(A1:F200=X1)))

※最初のA1はワークシートの左上隅を示すものなので、検索範囲に関わらずA1固定
※SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1)) ⇒ A1:F200で値がX1と一致するセルの行番号

>その「ある範囲」の中には検索したい値が入っているセルは1つしかありません。
というのが前提です。複数のセルがHITすると関係ないセルの値が返るので、
場...続きを読む

Qエクセルで選択した行以外を削除する方法

エクセル2003を利用しております。
選択した行を消すのではなく、選択した行以外の行を削除したいのですが、そういった機能はありますでしょうか。
ご存じの方教えていただけますと幸いです。

よろしくお願いします。M(_ _)M

Aベストアンサー

#3です。
それらしきものをVBAで作ってみました。
シートにコマンドボタンを1つ貼り付け。
そのクリックイベントに
Private Sub CommandButton1_Click()
d = Range("A65536").End(xlUp).Row
For i = d To 1 Step -1
If Intersect(Worksheets("Sheet1").Range("A" & i & ":Z" & i), selection) Is Nothing Then
Rows(i).EntireRow.Delete
Else
End If
Next i
End Sub
ーー
A列で最終行を判断しています。
利用操作
どの列でもよいからA-Z列の範囲内で残す行のセルを1列以上の範囲内で範囲指定する。
そしてボタンをクリック
(安易指定した以外の行が削除される)
注意
範囲指定してないで実行するとすべてデータが消える危険性アリ。
そういうことで使いずらいが、良い方法はなさそう。


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング