delete all blank rows in a spreadsheet
Thanks, Tom.
Works a treat.
"Tom Ogilvy" wrote:
Sub RemoveEmptyRows()
Dim xr As Long, xc As Integer, dRow As Boolean
Dim CalcType As Long, rng as Range
With Application
.ScreenUpdating = False
CalcType = .Calculation
.Calculation = xlCalculationManual
End With
set rng = Activesheet.UsedRange
For xr = rng(rng.count).row To 1 Step -1
dRow = True
For xc = 1 To 255
If Len(Trim(Cells(xr, xc))) 0 Then
dRow = False
Exit For
End If
Next xc
If dRow Then Rows(xr).Delete shift:=xlUp
Next xr
With Application
.ScreenUpdating = True
.Calculation = CalcType
End With
End Sub
--
Regards,
Tom Ogilvy
"Richard" wrote:
Hi Tom,
I am not as 'fluent' as you guys with VBA. What part of the code does your
suggested code replace? How do I fit into the 'delete blank rows' code?
Cheers,
Richard
"Tom Ogilvy" wrote:
String operations can be expensive, you might want to try a more conventional
approach:
Sub Tester()
Dim Start As Single
Dim num As Long, rw As Long
Dim rng as Range
num = 25000
Start = Timer
Set rng = ActiveSheet.UsedRange
For i = 1 To num
rw = rng(rng.Count).Row
Next
Debug.Print Timer - Start
Start = Timer
For i = 1 To num
rw = StrReverse(Val(StrReverse( _
ActiveSheet.UsedRange.Address)))
Next
Debug.Print Timer - Start
End Sub
I get the string approach as taking about 3 times as long.
For one command, obviously not a biggy, but no use getting into bad habits.
--
Regards,
Tom Ogilvy
"Richard" wrote:
Mike,
I haven't tested it with your example (i.e. row 82), but I changed the
relevant code to:
For xr = StrReverse(Val(StrReverse(ActiveSheet.UsedRange.Ad dress))) To 1
Step -1
which I think should overcome the problem.
"Mike Fogleman" wrote:
Nigel, StrReverse worked in XL2000 but UsedRange failed. Added
(Sheet1.UsedRange.Address) and it worked. However I was confused as to why
reverse the row number? If the UsedRange ended at row 28 then the code would
begin on row 82 and work upwards. That scenario would work. If the last row
was 82 then the code would begin on 28 and miss all the rows between.
Mike F
"Nigel" wrote in message
...
Are you using xl2002 or xl2003, the strReverse was not valid in prior
versions?
--
Cheers
Nigel
"Richard" wrote in message
...
Nigel,
Thanks. When I ran the code an error was thown up. The debugger
highlighted
this row
For xr = Val(StrReverse(UsedRange.Address)) To 1 Step -1
Should I have selected the used range?
"Nigel" wrote:
Sub RemoveEmptyRows()
Dim xr As Long, xc As Integer, dRow As Boolean
Dim CalcType As Long
With Application
.ScreenUpdating = False
CalcType = .Calculation
.Calculation = xlCalculationManual
End With
For xr = Val(StrReverse(UsedRange.Address)) To 1 Step -1
dRow = True
For xc = 1 To 255
If Len(Trim(Cells(xr, xc))) 0 Then
dRow = False
Exit For
End If
Next xc
If dRow Then Rows(xr).Delete shift:=xlUp
Next xr
With Application
.ScreenUpdating = True
.Calculation = CalcType
End With
End Sub
--
Cheers
Nigel
"Richard" wrote in message
...
How do I delete entire rows in a spreadsheet with a VBA macro?
I can't use autofilter (or similar techniques) as not all of the
columns
have data in every non blank row i.e. if I sorted by column A, there
may
be a
row with a blank cell in column A, but another column (say AX) that
may be
out of view could have data in it.
|