Vba across the workbook to delete blank lines and blank columns-CodePudding

A great god, and I'd like to work in a book, by vba delete another worksheet in the workbook empty lines, null columns
The code is as follows:

 
Windows (" 1. XLSX "). Activate 
Dim LastRow As Long, r As Long 
LastRow=ActiveSheet. UsedRange. Rows. Count 
LastRow=LastRow + ActiveSheet. UsedRange. Row - 1 


For r=LastRow To Step 1-1 
If WorksheetFunction. CountA (Rows (r))=0 Then Rows (r). The Delete 
Next r 


Dim LastColumn As Long, As Long c 
LastColumn=ActiveSheet. UsedRange. Columns. The Count 
LastColumn=LastColumn + ActiveSheet. UsedRange. Column 
For c=LastColumn To Step 1-1 
If WorksheetFunction. CountA (Columns (c))=0 Then the Columns (c) Delete 
Next c

But always delete the blank line of the vba in the workbook worksheet, null columns, this need how to change?

CodePudding user response:

Because your Rows and Columns not plus prefix ActiveSheet.
The modified code is as follows:

 Sub test () 
Windows (" 1. XLSX "). Activate 
Dim LastRow As Long, r As Long 
LastRow=ActiveSheet. UsedRange. Rows. Count 
LastRow=LastRow + ActiveSheet. UsedRange. Row - 1 


For r=LastRow To Step 1-1 
If WorksheetFunction. CountA (ActiveSheet. Rows (r))=0 Then ActiveSheet. Rows (r). The Delete 
Next r 

Dim LastColumn As Long, As Long c 
LastColumn=ActiveSheet. UsedRange. Columns. The Count 
LastColumn=LastColumn + ActiveSheet. UsedRange. Column 
For c=LastColumn To Step 1-1 
If WorksheetFunction. CountA (ActiveSheet. Columns (c))=0 Then ActiveSheet. Columns (c) Delete 
Next c 
End Sub

Download address:
Link: https://pan.baidu.com/s/1yB-R9KTzggYU5beav8IllA