delete rows

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ivor Williams - 29 Dec 2007 15:11 GMT

I've a spreadsheet of inventory information that was exported from
QuickBooks. The resulting spreadsheet contains a number of rows with no
information in the column I want to sort on, so I would like to
automatically delete those rows. I'm totally new to writing VBA for Excel,
but I'm quite comfortable with it in Access, so understand it somewhat. I
also understand I can modify the QuickBooks report so the spreadsheet is
created in a format that will work, but I would like some assistance with
the code so I can start to learn what I'm doing in Excel.

Thanks,
Ivor

Reply to this Message

JLGWhiz - 29 Dec 2007 15:30 GMT

You will need to adjust to your file criteria.

Sub DelRw()
lstRw = Cells(Rows.Count, 1).End(xlUp).Row
For i = lstRw To 2 Step -1 'Assumes Header Row
If Cells(i, 1) = "" Then 'Change col 1 to actual
Cells(i, 1).EntireRow.Delete
End If
Next
End Sub

> I've a spreadsheet of inventory information that was exported from
> QuickBooks. The resulting spreadsheet contains a number of rows with no
[quoted text clipped - 7 lines]
> Thanks,
> Ivor

Reply to this Message

Ivor Williams - 29 Dec 2007 15:59 GMT

Thank you for the suggestion. Would you be kind enough to explain the code
so I understand what each line means.

Ivor

> You will need to adjust to your file criteria.
>
[quoted text clipped - 19 lines]
>> Thanks,
>> Ivor

Reply to this Message

JLGWhiz - 29 Dec 2007 18:35 GMT

Sure:

Sub DelRw() 'This is the title line which you can use when
'calling this macro. Just type DelRw in other code
'within the same workbook.

lstRw = Cells(Rows.Count, 1).End(xlUp).Row 'This line sets the
'variable lstRw equal to the number of the last row
'containing data.

For i = lstRw To 2 Step -1 'Assumes Header Row and establishes a
'beginning for a loop with a
numerical limit.
'Since you are deleting entire rows,
it starts
'at the bottom so no rows will be
skipped due
' to the automatic upward shift
after delete.

If Cells(i, 1) = "" Then 'Change col 1 to actual and sets the
'criteria for deleting a
row. In this case
'it starts with the last row
with data in
'column A.

Cells(i, 1).EntireRow.Delete 'Executes the delete action for the cell
'if it met the criteria,
which in this case is
'if the cell = null string.

End If 'Closes the if block

Next 'Goes to nest item up on the active sheet and
'repeats the process until the For limits expire.

End Sub 'Closes the procedure.

> Thank you for the suggestion. Would you be kind enough to explain the code
> so I understand what each line means.
[quoted text clipped - 23 lines]
> >> Thanks,
> >> Ivor

Reply to this Message

RichardSchollar - 29 Dec 2007 19:00 GMT

Hi Ivor

If you have a lot of rows (ie into the 1000s) then using autofilter
will increase the spped of execution considerably over looping:

Sub RemoveBlanks()
Dim r As Range
Set r = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row) 'spec the
range to run from A1 to the last row
'note that presumed header in row 1. Change "A" column to the actual
column
With r
.AutoFilter field:=1, Criteria1:="=" 'filter range for blanks
.Offset(1).EntireRow.Delete 'offset so don't delete header row
(only visible cells will be deleted)
.AutoFilter 'turn off autofilter
End With
End Sub

Best regards

Richard

> I've a spreadsheet of inventory information that was exported from
> QuickBooks. The resulting spreadsheet contains a number of rows with no
[quoted text clipped - 7 lines]
> Thanks,
> Ivor

Reply to this Message

Ivor Williams - 31 Dec 2007 17:17 GMT

I've tried the following and get an Error 400. If I step into the code, it
gets hung up on the second line. G is the actual column I want to check for
null values.
Sub RemoveBlanks()

Dim r As Range
Set r = Range("A1:A" & Cells(Rows.Count, "G").End(x1Up).Row)
With r
.AutoFilter field:=1, Criteria1:="="
.Offset(1).EntireRow.Delete
.AutoFilter
End With

End Sub

Thanks,
Ivor

> Hi Ivor
>
[quoted text clipped - 31 lines]
>> Thanks,
>> Ivor

Reply to this Message