rolly
02-17-2011, 09:58 AM
My vba code currently removes all the rows with a value of zero in the "B" column. It works well, but I also need it to remove the cell borders at the same time it removes the rows.
This is so when I print, it will only print the pages with values and not what it does now which is several more pages of blank cells with borders around them.
Sub Remove_rows_zeros_Bcolumn()
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
Sheets("Testblocks-to-buy-list").Select
With ActiveSheet
.Select
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView
.DisplayPageBreaks = False
'Set the first and last row to loop through
Firstrow = .UsedRange.Cells(4).Row
Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
For Lrow = Lastrow To Firstrow Step -1
'We check the values in the B column in this example
With .Cells(Lrow, "B")
If Not IsError(.Value) Then
If .Value = 0 Then .EntireRow.Delete
End If
End With
Next Lrow
End With
ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub
Note: I pulled this code from the web and modified it, but I can't remember where, I'd give credit to them if I knew where I got it.
Thank you for your help,
Rolly
This is so when I print, it will only print the pages with values and not what it does now which is several more pages of blank cells with borders around them.
Sub Remove_rows_zeros_Bcolumn()
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
Sheets("Testblocks-to-buy-list").Select
With ActiveSheet
.Select
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView
.DisplayPageBreaks = False
'Set the first and last row to loop through
Firstrow = .UsedRange.Cells(4).Row
Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
For Lrow = Lastrow To Firstrow Step -1
'We check the values in the B column in this example
With .Cells(Lrow, "B")
If Not IsError(.Value) Then
If .Value = 0 Then .EntireRow.Delete
End If
End With
Next Lrow
End With
ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub
Note: I pulled this code from the web and modified it, but I can't remember where, I'd give credit to them if I knew where I got it.
Thank you for your help,
Rolly