Macro/VBA to delete rows in Spreadsheet

[Colin Foster]

Hi all... looking for wisdom...

I have a text report that is pulled into Excel which I then sort & then
manually delete certain rows (basically those with header/footer/total
information from the original report). What I would like to do is create a
macro/VBA code to do this automatically so that, for example, it looks down
Column A for any cells with "A/C No" in & deletes any complete rows that
have this information & then looks for the next "deletable header" e.g.
"Report Total".
It then needs to check column B for an "@" symbol or blank cells & again
delete the whole row (blank cells in column B will have data elsewhere that
I want to remove)

Finally, ideally, it then needs to convert the text in columns B & D to
Proper case

Any suggestions would be welcome,
Regards
Colin Foster

 

[Nigel]

Some code for you to try and adapt. I refer to Sheet1 so change the
reference if the sheet is named something else.

Public Sub SelectiveDelete()
Dim LRowData As Long, ir As Long

With Sheets("Sheet1")
' detect last row of data in column A
LRowData = Cells(Rows.Count, "A").End(xlUp).Row

' scan list testing if the value is in range
For ir = LRowData To 1 Step -1
If Trim(Cells(ir, 1).Value) = "A/C No" Or _
Trim(Cells(ir, 1).Value) = "Report Total" Or _
InStr(1, Trim(Cells(ir, 2).Value), "@") > 0 Or _
Len(Trim(Cells(ir, 2).Value)) = 0 _
Then Rows(ir).EntireRow.Delete Shift:=xlUp
Next ir
End With

End Sub

 

[Colin Foster]

Nigel,
Thanks for this coding... I'll try it out on Monday when I get back to th
eoffice & report back to you.
Regards
Colin

 

[Ardus Petus]

You are using a With instruction, but forgot the dots in the code (eg:
..Cells instead of Cells)

Public Sub SelectiveDelete()
Dim LRowData As Long, ir As Long

With Sheets("Sheet1")
' detect last row of data in column A
LRowData = .Cells(Rows.Count, "A").End(xlUp).Row

' scan list testing if the value is in range
For ir = LRowData To 1 Step -1
If Trim(.Cells(ir, 1).Value) = "A/C No" Or _
Trim(.Cells(ir, 1).Value) = "Report Total" Or _
InStr(1, Trim(.Cells(ir, 2).Value), "@") > 0 Or _
Len(Trim(.Cells(ir, 2).Value)) = 0 _
Then .Rows(ir).Delete Shift:=xlUp
Next ir
End With


HTH

 

[Colin Foster]

Ardus,
Again, thanks to you, too.

Colin

You are using a With instruction, but forgot the dots in the code (eg:
.Cells instead of Cells)

Public Sub SelectiveDelete()
Dim LRowData As Long, ir As Long

With Sheets("Sheet1")
' detect last row of data in column A
LRowData = .Cells(Rows.Count, "A").End(xlUp).Row

' scan list testing if the value is in range
For ir = LRowData To 1 Step -1
If Trim(.Cells(ir, 1).Value) = "A/C No" Or _
Trim(.Cells(ir, 1).Value) = "Report Total" Or _
InStr(1, Trim(.Cells(ir, 2).Value), "@") > 0 Or _
Len(Trim(.Cells(ir, 2).Value)) = 0 _
Then .Rows(ir).Delete Shift:=xlUp
Next ir
End With


HTH

 

[Nigel]

Um ... my mistake, I added this after the code was written for the active
sheet. Apologies. It still works if the sheet you are acting on is
selected!
But Ardus has provided the correct method.