delete all blank rows in a spreadsheet

  • Thread starter Thread starter Guest
  • Start date Start date
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.
 
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
 
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
 
Thanks, Tom.

Works a treat.

Tom Ogilvy said:
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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Eliminate blanks while copy row and transpose 11
hiding blank rows 3
convert columns to rows 6
Macro for Deleting rows with balnk cell 3
Problem with deleting blank rows or last row 4
delete rows in a given range 1
Print variable number of rows of spreadsheet 1
Getting Last Non-Blank Row Number With VBA 2

Back
Top