default_name
Board Regular
- Joined
- May 16, 2018
- Messages
- 180
- Office Version
- 365
- 2016
- Platform
- Windows
- MacOS
I have a workbook that contains several different sheets of data.
Two sheets, in particular, occasionally end up being unnecessarily long at the bottom/end of the sheet.
There are many instances where the blank rows extend well beyond the data.
This has increased my file size dramatically, making it much more difficult to work with.
I am trying to implement code that will go through each of these worksheets, and remove all of the blank rows that are coming after the data.
Here is what I have right now. I am thinking that there must be a more simple way to do this, because when I run this code it takes forever to run, and it usually ends up crashing Excel in the end.
Thanks in advance
Two sheets, in particular, occasionally end up being unnecessarily long at the bottom/end of the sheet.
There are many instances where the blank rows extend well beyond the data.
This has increased my file size dramatically, making it much more difficult to work with.
I am trying to implement code that will go through each of these worksheets, and remove all of the blank rows that are coming after the data.
Here is what I have right now. I am thinking that there must be a more simple way to do this, because when I run this code it takes forever to run, and it usually ends up crashing Excel in the end.
VBA Code:
Sheets("Data1").Select
Dim x As Long
With ActiveSheet
For x = .Cells.SpecialCells(xlCellTypeLastCell).Row To 1 Step -1
If WorksheetFunction.CountA(.Rows(x)) = 0 Then
ActiveSheet.Rows(x).Delete
End If
Next
End With
Sheets("Data2").Select
Dim y As Long
With ActiveSheet
For y = .Cells.SpecialCells(xlCellTypeLastCell).Row To 1 Step -1
If WorksheetFunction.CountA(.Rows(y)) = 0 Then
ActiveSheet.Rows(y).Delete
End If
Next
End With
Thanks in advance