S
slo
I borrowed this sub from a site I ran across but it takes about 25-30
seconds on a P4. I know there is a better way, but I don't have the
brain to code. I would appreciate very much an offer for any
improvements.
I am trying to remove all rows where the F column has no value.
Thank you all.
Dan
Sub DeleteEmptyRowsWhereFIsEmpty()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView
Firstrow = ActiveSheet.UsedRange.Cells(1).Row
Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1
With ActiveSheet
.DisplayPageBreaks = False
For Lrow = Lastrow To Firstrow Step -1
If IsError(.Cells(Lrow, "F").Value) Then
'Do nothing, This'll avoid a error if there is a error
in the cell
ElseIf .Cells(Lrow, "F").Value = "" Then .Rows(Lrow).Delete
'This will delete each row with the blan value in
Column F, case sensitive.
End If
Next
End With
ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub
seconds on a P4. I know there is a better way, but I don't have the
brain to code. I would appreciate very much an offer for any
improvements.
I am trying to remove all rows where the F column has no value.
Thank you all.
Dan
Sub DeleteEmptyRowsWhereFIsEmpty()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView
Firstrow = ActiveSheet.UsedRange.Cells(1).Row
Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1
With ActiveSheet
.DisplayPageBreaks = False
For Lrow = Lastrow To Firstrow Step -1
If IsError(.Cells(Lrow, "F").Value) Then
'Do nothing, This'll avoid a error if there is a error
in the cell
ElseIf .Cells(Lrow, "F").Value = "" Then .Rows(Lrow).Delete
'This will delete each row with the blan value in
Column F, case sensitive.
End If
Next
End With
ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub