Macro for Deleting Specific Rows?

[cardan]

Hello all,

I am seeking help regarding a new accounting system that imports info
into excel. When it imports, it leaves blank rows as well as rows
with "----------" in it. It also gives totals and subtotals under
certain categories.

I would like to delete the blank rows, delete the rows with the
"---------" as well as delete the rows that have the category totals.
Is there a macro out there where I could identify and delete rows with
these criteria? Any help would be greatly appreciated. Thank you for
your time in advance.

 

[Guest]

There have been a large number of postings discussing deleteing rows. A
quick search of the group should yeild many examples. Always start at the
bottom of the sheet when deleting.

 

[Guest]

Sub delete_rows()

Dim RowNdx As Long
Dim LastRow As Long
LastRow = ActiveSheet.UsedRange.Rows.Count
Try this:

For RowNdx = LastRow To 1 Step -1
If Cells(RowNdx, "A").Value = "" Or Cells(RowNdx, "A").Value = "----------"
Or Cells(RowNdx, "A").Value = "total" Or Cells(RowNdx, "A").Value =
"subtotal" Then 'Change the "A" to another Column is needed
Rows(RowNdx).Delete
End If
Next RowNdx
End Sub

Regards,
Ryan---

 

[cardan]

Sub delete_rows()

Dim RowNdx As Long
Dim LastRow As Long
LastRow = ActiveSheet.UsedRange.Rows.Count
Try this:

For RowNdx = LastRow To 1 Step -1
If Cells(RowNdx, "A").Value = "" Or Cells(RowNdx, "A").Value = "----------"
Or Cells(RowNdx, "A").Value = "total" Or Cells(RowNdx, "A").Value =
"subtotal" Then 'Change the "A" to another Column is needed
Rows(RowNdx).Delete
End If
Next RowNdx
End Sub

Regards,
Ryan---
--
RyGuy







- Show quoted text -

RyGuy, Thank you for the response. The Macro works, for the most
part, however there are some issues on my end. I realized that when I
import the numbers from the accounting program, "blank" cells are
actually not blank. Even though there are no number or anything in
them, Excel still picks up something in them. When I highlight the row
in question and hit delete, then run the macro, it will only then read
the rows as blank and delete them. I tried changing your formula from
"" to 0 but that also does not work.

Also the rows with "totals" in them that I need to get rid of contain
the word total with the heading name as well. ie Total Finance, Total
Design, etc... Is there a way I can get rid of a row in a column
contains the word "Total"?

Thank you for your help. I am very appreciative!

 

[Guest]

Sorry, for the delayed follow-up; extremely busy these past several days.
Hope this does what you want (notice, I used Column B as a helper column;
your Column B must be empty...Click on B and shift right one Column).


Sub delete_rows()

Dim RowNdx As Long
Dim LastRow As Long
LastRow = ActiveSheet.UsedRange.Rows.Count

Range("B1").Select
ActiveCell.FormulaR1C1 = "=TRIM(RC[-1])"
Selection.AutoFill Destination:=Range("B1:B22") ' Change to suit your needs
Range("B1:B22").Select
Range("C1").Select

For RowNdx = LastRow To 1 Step -1
If Cells(RowNdx, "B").Value = "" Or Cells(RowNdx, "B").Value = "----------" _
Or Cells(RowNdx, "B").Value = "total" Or Cells(RowNdx, "B").Value = _
"subtotal" Then 'Change the "B" to another Column is needed
Rows(RowNdx).Delete
End If
Next RowNdx
End Sub

 

[cardan]

Sorry, for the delayed follow-up; extremely busy these past several days.
Hope this does what you want (notice, I used Column B as a helper column;
your Column B must be empty...Click on B and shift right one Column).

Sub delete_rows()

Dim RowNdx As Long
Dim LastRow As Long
LastRow = ActiveSheet.UsedRange.Rows.Count

Range("B1").Select
ActiveCell.FormulaR1C1 = "=TRIM(RC[-1])"
Selection.AutoFill Destination:=Range("B1:B22") ' Change to suit your needs
Range("B1:B22").Select
Range("C1").Select

For RowNdx = LastRow To 1 Step -1
If Cells(RowNdx, "B").Value = "" Or Cells(RowNdx, "B").Value = "----------" _
Or Cells(RowNdx, "B").Value = "total" Or Cells(RowNdx, "B").Value = _
"subtotal" Then 'Change the "B" to another Column is needed
Rows(RowNdx).Delete
End If
Next RowNdx
End Sub

--
RyGuy

RyGuy, Thank you for the response. The Macro works, for the most
part, however there are some issues on my end. I realized that when I
import the numbers from the accounting program, "blank" cells are
actually not blank. Even though there are no number or anything in
them, Excel still picks up something in them. When I highlight the row
in question and hit delete, then run the macro, it will only then read
the rows as blank and delete them. I tried changing your formula from
"" to 0 but that also does not work.

Also the rows with "totals" in them that I need to get rid of contain
the word total with the heading name as well. ie Total Finance, Total
Design, etc... Is there a way I can get rid of a row in a column
contains the word "Total"?

Thank you for your help. I am very appreciative!- Hide quoted text -

- Show quoted text -

Works great, sorry for my delay in response as well. I greatly
appreciate the effort!

 

[gimme_this_gimme_that]

The solutions posted so far work but work slowly on worksheets where
you have tens of thousands of rows. For that it's better to create a
tag column having a 1 for save and 0 for delete, then sort on the tag
column, select rows having the 0 and delete them all at once.