Hi everyone. I need help with an Excel worksheet. I have NEVER used the VBE before, this is my first time, so go easy on me.
The problem:
I want to delete all the empty rows in a worksheet. I have done a search and found this code:
Sub DeleteBlankRows1()
'Deletes the entire row within the selection if the ENTIRE row contains no data.
'We use Long in case they have over 32,767 rows selected.
Dim i As Long
'We turn off calculation and screenupdating to speed up the macro.
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
'We work backwards because we are deleting rows.
For i = Selection.Rows.Count To 1 Step -1
If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then
Selection.Rows(i).EntireRow.Delete
End If
Next i
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub
Of course, it means absolutely nothing to me, it's like a foreign language. But it also gives these instructions:
Open Excel.
Push Alt+F11 to open the VBE (Visual Basic Editor).
Go to Insert>Module.
Copy the code and paste it in the new module.
Push Alt+Q to return to Excels normal view.
Push Alt+F8 and then select the macro name and click Run. Or select Options and assign a shortcut key.
The first two are no problem but when I try the third, i.e. "Go to Insert>Module." The option to do so is not available. The screen has two white panes on the left, the top pane is labeled "Projects - No open projects", the bottom pane is labeled "Properties", The main screen, which takes up most of the screen, is Grey.
How do I proceed?
The code is taken from this site:
The problem:
I want to delete all the empty rows in a worksheet. I have done a search and found this code:
Sub DeleteBlankRows1()
'Deletes the entire row within the selection if the ENTIRE row contains no data.
'We use Long in case they have over 32,767 rows selected.
Dim i As Long
'We turn off calculation and screenupdating to speed up the macro.
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
'We work backwards because we are deleting rows.
For i = Selection.Rows.Count To 1 Step -1
If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then
Selection.Rows(i).EntireRow.Delete
End If
Next i
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub
Of course, it means absolutely nothing to me, it's like a foreign language. But it also gives these instructions:
Open Excel.
Push Alt+F11 to open the VBE (Visual Basic Editor).
Go to Insert>Module.
Copy the code and paste it in the new module.
Push Alt+Q to return to Excels normal view.
Push Alt+F8 and then select the macro name and click Run. Or select Options and assign a shortcut key.
The first two are no problem but when I try the third, i.e. "Go to Insert>Module." The option to do so is not available. The screen has two white panes on the left, the top pane is labeled "Projects - No open projects", the bottom pane is labeled "Properties", The main screen, which takes up most of the screen, is Grey.
How do I proceed?
The code is taken from this site: