Macro/VBA to delete rows in Spreadsheet
 

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

Macro/VBA to delete rows in Spreadsheet
 

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

--
Cheers
Nigel



"Colin Foster" wrote in message
...
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

Macro/VBA to delete rows in Spreadsheet
 

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


"Nigel" wrote in message
...
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

--
Cheers
Nigel



"Colin Foster" wrote in message
...
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

Macro/VBA to delete rows in Spreadsheet
 

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
--
AP

"Nigel" a écrit dans le message de news:
...
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

--
Cheers
Nigel



"Colin Foster" wrote in message
...
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

Macro/VBA to delete rows in Spreadsheet
 

Ardus,
Again, thanks to you, too.

Colin


"Ardus Petus" wrote in message
...
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
--
AP

"Nigel" a écrit dans le message de news:
...
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

--
Cheers
Nigel



"Colin Foster" wrote in message
...
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

Macro/VBA to delete rows in Spreadsheet
 

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.

--
Cheers
Nigel



"Ardus Petus" wrote in message
...
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
--
AP

"Nigel" a écrit dans le message de news:
...
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

--
Cheers
Nigel



"Colin Foster" wrote in message
...
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