delete empty rows & columns

[zhfgtj27]

I have a massive spreadsheet, with data range starting from C3. The first 2 rows and columns are the headers. I want to create a macro to delete the rows and columns that are empty at the data range. I appreciate all the help.

Thanks,
Fan

 

[Smitty]

Welcome to the Board!

See: http://www.contextures.com/xlfaqApp.html#Unused

HTH,

 

[zhfgtj27]

Welcome to the Board!

See: http://www.contextures.com/xlfaqApp.html#Unused

HTH,

Hi Smitty, I didn't see any relevant info on the webpage you provided.

 

[Smitty]

Really?

Try this section: To programatically reset the used range,

 

[mirabeau]

I have a massive spreadsheet, with data range starting from C3. The first 2 rows and columns are the headers. I want to create a macro to delete the rows and columns that are empty at the data range. I appreciate all the help.

Thanks,
Fan

hi,

as alternative, here's a code you might like to try

Sub del_empty_rows_cols()

Dim a As Range, u(), v()
Dim r As Long, c As Long, i As Long
Dim x As Long, y As Long

Set a = Cells
r = a.Find("*", , , , xlByRows, xlPrevious).Row
c = a.Find("*", , , , xlByColumns, xlPrevious).Column
ReDim u(1 To r, 1 To 1), v(1 To 1, 1 To c)

For i = 3 To r
    If a(i, 2).End(2).Column = Columns.Count Then
        u(i, 1) = 1
        x = x + 1
    End If
Next i

For i = 3 To c
    If a(2, i).End(4).Row = Rows.Count Then
        v(1, i) = 1
        y = y + 1
    End If
Next i

a(c + 1).Resize(r) = u
a(r + 1, 1).Resize(, c) = v

With a(1).Resize(r + 1, c + 1)
    .Sort a(c + 1), Orientation:=xlTopToBottom, Header:=xlNo
    .Sort a(r + 1, 1), Orientation:=xlLeftToRight, Header:=xlNo
    If x > 0 Then .Rows(1).Resize(x).Delete xlUp
    If y > 0 Then .Columns(1).Resize(, y).Delete xlToLeft
End With

End Sub