multi tab workbook
 

Working with numerous sheets in one book is new for me so please excuse my
ignorance here. My question is I have this code that deletes all rows on a
sheet that doesn't meet the specific criteria of the text "Assets" in column
B. Now I need to enhance this code for a multi sheet Excel book.

My book has 6 tabs labled Assets1, Assets2, Assets3 and so on. I only want
to keep information in Assets1 with the text "GESA CC" in Column labled
"OrigTbl" which is column D and the text "4-$" in Column C labled "Match".
Then in Assets 2 I only want to keep rows that meet the criteria of " GESACC"
in column D with the text of "GESACC Only". I have many other sheets that I
will be able to code if I just see how I would code these two sheets.

One other question, which is better to load all the records on each
individual sheet and detete from the entire list on each page or just copy
the rows I want with the above criteria from the sheet titled "All REcords".
I appreciate your help. Thanks so much

Sub DeleteRows()
Dim LastRow As Long
Dim RowNdx As Long
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
For RowNdx = LastRow To 1 Step -1
If StrComp(Cells(RowNdx, "B"), "assets", _
vbTextCompare) < 0 Then
Rows(RowNdx).Delete
End If
Next RowNdx
End sub

multi tab workbook
 

I think I'd do something like:

Sub DeleteRows2()
Dim LastRow As Long
Dim RowNdx As Long

'do 1st sheet
with worksheets("assets1")
'use column D for assets1
LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
For RowNdx = LastRow To 1 Step -1
If StrComp(.Cells(RowNdx, "D").value, "gesa cc", vbTextCompare) = 0 _
or strcomp(.cells(rowndx,"C").value, "4-$", vbtextcompare) = 0 Then
'at least one failed, maybe both, so delete it
.Rows(RowNdx).Delete
End If
Next RowNdx
end with

'do 2nd sheet
'...

End sub

Notice the "with" statement. Everything that starts with a dot belongs to that
with object (Worksheets("assets1") in this case.)

That includes .cells, and .rows.



JOUIOUI wrote:

Working with numerous sheets in one book is new for me so please excuse my
ignorance here. My question is I have this code that deletes all rows on a
sheet that doesn't meet the specific criteria of the text "Assets" in column
B. Now I need to enhance this code for a multi sheet Excel book.

My book has 6 tabs labled Assets1, Assets2, Assets3 and so on. I only want
to keep information in Assets1 with the text "GESA CC" in Column labled
"OrigTbl" which is column D and the text "4-$" in Column C labled "Match".
Then in Assets 2 I only want to keep rows that meet the criteria of " GESACC"
in column D with the text of "GESACC Only". I have many other sheets that I
will be able to code if I just see how I would code these two sheets.

One other question, which is better to load all the records on each
individual sheet and detete from the entire list on each page or just copy
the rows I want with the above criteria from the sheet titled "All REcords".
I appreciate your help. Thanks so much

Sub DeleteRows()
Dim LastRow As Long
Dim RowNdx As Long
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
For RowNdx = LastRow To 1 Step -1
If StrComp(Cells(RowNdx, "B"), "assets", _
vbTextCompare) < 0 Then
Rows(RowNdx).Delete
End If
Next RowNdx
End sub

--

Dave Peterson