Quote Originally Posted by Ron Coderre
Hi, BigBas

I have a couple suggestions...

First, if it isn't already, the DeleteBlankRows code should be in a General Module in your Personal.xls. That way, it will be available to ALL workbooks.

Second, I'd use slightly more durable and efficient code. There's no need to start at the absolute bottom of the sheet if the used range only extends to Row_45, right?

Sub DeleteBlankRows()
Dim lngEndRow As Long
Dim lngCtr As Long
Dim CurrCalcSetting

' Check if the sheet is protected
If ActiveSheet.ProtectContents Then
   MsgBox ActiveSheet.Name & " is protected"
   Exit Sub
End If

With Application
   ' Store the current calculation setting (to restore it later)
   CurrCalcSetting = .Calculation
   .Calculation = xlCalculationManual
   .ScreenUpdating = False
End With

On Error GoTo errTrap

With ActiveSheet
   
   lngEndRow = .UsedRange.Rows.Count
         
   For lngCtr = lngEndRow To 3 Step -1
      If .Cells(lngCtr, 3) = "" Then
         .Cells(lngCtr, 3).EntireRow.Delete
      End If
   Next lngCtr
End With

errTrap:
' Reset efficiency settings
With Application
   .Calculation = CurrCalcSetting
   .ScreenUpdating = True
End With

If Err.Number <> 0 Then
   MsgBox "Problem was encountered"
End If
End Sub
Does that help?
Hi Ron,
I'm amazed at your skill in writing tight code! I'm quite weak at vba in general and have a need to perform a routine for which I'm able to code, but I need to apply it only to the active sheet.

I've studied the code you gave BigBas. If I understand it, lngEndRow (what's lng?) is the last row of the active sheet and lngCtr is a counter that starts at the last row and goes through row three (I presume the first two rows are header). What I'm quite murky about is the if statement and the following one. I don't understand what the "3" is in either one of them.

Btw, the routine I'm working on is to sort a sheet of a couple hundred lines after first removing all hard page breaks and then inserting them back in the new sort where they belong at the beginning of each of a dozen or two groups.

Thanks for sharing your expertise!