delete all blank rows in a spreadsheet

[Guest]

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.

 

[Guest]

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

 

[Guest]

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

 

[Guest]

Thanks, Tom.

Works a treat.

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

 

[Mike Fogleman]

The old double-reverse trick. You must like football!

 

[Nigel]

World Cup is coming!!

--
Cheers
Nigel

The old double-reverse trick. You must like football!