Should i enter the code from
http://www.cpearson.com/excel/deleting.htm in
the same macro as the....
DeleteBlankRows ScanRange:=Range("A1:A100")
.... function or should this be the code from
http://www.cpearson.com/excel/deleting.htm be set up elsewhere? (eg. as a
module?)
Thanks,
David
"Chip Pearson" wrote:
You can adapt the DeleteBlankRows code on
http://www.cpearson.com/excel/deleting.htm to your needs. Specifically,
'---------------------------
Change
'---------------------------
Sub DeleteBlankRows(Optional WorksheetName As Variant)
' To
Sub DeleteBlankRows(ScanRange As Range, Optional WorksheetName As Variant)
'---------------------------
Delete
'---------------------------
Set Rng = WS.Cells.Find(what:="*", after:=WS.Cells(WS.Rows.Count,
WS.Columns.Count), lookat:=xlPart, _
searchorder:=xlByColumns, searchdirection:=xlPrevious, MatchCase:=False)
'------------------------
Add:
'------------------------
Dim FirstRow As Long
'------------------------
Change
'------------------------
LastRow = Rng.Row
' To
LastRow = ScanRange.Cells(ScanRange.Cells.Count).Row
FirstRow = ScanRange(1, 1).Row
'------------------------
Change
'------------------------
For RowNum = LastRow To 1 Step -1
' To
For RowNum = LastRow To FirstRow Step -1
You can then call this code with a procedure call like
Sub DoDelete()
DeleteBlankRows ScanRange:=Range("A1:A100")
End Sub
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)
"DavidHawes" wrote in message
...
Just wanted to move this up the forums as my subsequent question (below)
would be lost otherwise...
---------------------
As an extra, if I wanted to specify a range of cells that, if blank, would
result in the deletion of the rows where those cells lie, how would I do
this?
Thanks again.
David
"Gord Dibben" wrote:
David
Try this macro to delete just empty rows.
Sub DeleteEmptyRows()
''only if entire row is blank
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = LastRow To 1 Step -1
If Application.CountA(Rows(r)) = 0 Then
Rows(r).Delete
End If
Next r
End Sub
Gord Dibben MS Excel MVP
On Fri, 23 Feb 2007 05:33:29 -0800, DavidHawes
wrote:
Hi,
I've set up a macro which re-organises an excel spreadsheet into a
format
that enables me to import the data contained within it directly into an
Access database i've set up.
This works perfectly.
However, I want the macro to delete out any lines of my table (which is
fixed in size) that do not contain any data.
Is this possible? If so, can someone please explain what code I need to
enter to get my macro to do this?
Many thanks,
David :-)