Delete everything to the right and below my data set

[dougebowl]

I am looking for a Macro that will delete all empty columns to the right of my data set and then delete all rows below my data set.

Row 1 will always contain the column headings to be used to determine the last column (example: Row 1 contains headings in columns A-D, I need all columns E - the last column in spreadsheet deleted)
Column A will always contain a value to be used to determine the last row (example: Column A contains data in Rows 1-4, I need all rows 5 - last row in spreadsheet deleted)

I have found posts that help determine the last column/row, but, I am failing at getting the Macro to select everything to the right and below and delete. I need this to happen as someone could have inadvertently added some formatting to a cell and I need to insure that the only data left in the spreadsheet is the data table.

All advanced help is greatly appreciated.

 

[Joe4]

Here is one way:

Sub DeleteRC()

    Dim lc As Long
    Dim lr As Long
    Dim ec As Long
    Dim er As Long
   
'   Find last column in row 1 with data
    lc = Cells(1, Columns.Count).End(xlToLeft).Column
   
'   Find last row in column A with data
    lr = Cells(Rows.Count, "A").End(xlUp).Row
   
'   Find ending column
    ec = Range("A1").SpecialCells(xlLastCell).Column
   
'   Find ending row
    er = Range("A1").SpecialCells(xlLastCell).Row
   
'   Delete extra columns
    If ec > lc Then
        Range(Cells(1, lc + 1), Cells(1, ec)).EntireColumn.Delete
    End If
   
'   Delete extra rows
    If er > lr Then
        Rows(lr + 1 & ":" & er).Delete
    End If
   
End Sub

I added lot of documentation so you can see what it is doing each step along the way.

 

[dougebowl]

This does not seem to be working as I need. I set up a test sheet and ran the Macro and it did not delete the columns and rows as expected. My dataset goes from A1 to D5.

I then added a value in H1 and F10. I would have expected all the columns beginning with column E and to the right to be deleted. Then, all the rows beginning with row 6 and below to be deleted.

All further assistance is greatly appreciated.

 

[Joe4]

See if this makes any difference:

Sub DeleteRC()

    Dim lc As Long
    Dim lr As Long
    Dim ec As Long
    Dim er As Long
   
    ActiveWorkbook.Save

'   Find last column in row 1 with data
    lc = Cells(1, Columns.Count).End(xlToLeft).Column
   
'   Find last row in column A with data
    lr = Cells(Rows.Count, "A").End(xlUp).Row
   
'   Find ending column
    ec = Range("A1").SpecialCells(xlLastCell).Column
   
'   Find ending row
    er = Range("A1").SpecialCells(xlLastCell).Row
   
'   Delete extra columns
    If ec > lc Then
        Range(Cells(1, lc + 1), Cells(1, ec)).EntireColumn.Delete
    End If
   
'   Delete extra rows
    If er > lr Then
        Rows(lr + 1 & ":" & er).Delete
    End If
   
End Sub

 

[dougebowl]

Unfortunately, this did not make a difference. The values in H1 and F10 remain. I would expect them to disappear as the columns/rows will be deleted.

 

[Joe4]

Looks like you violated your own rule!

Row 1 will always contain the column headings to be used to determine the last column

I then added a value in H1 and F10

You said that Row 1 is the header row that can be used to determine the last column, but then you added values you wanted deleted from this row (H1).
So I am guessing that statement isn't actually an accurate representation of what you want. It sounds you want delete everything AFTER the first blank in row 1.
Similarly, I am assuming that maybe you want the same sort of thing for column 1 (which will only work if there are no blanks in your data).

If those assumptions are correct, and you have at least two columns and two rows (including the header row), then this should work:

Sub DeleteRC()

    Dim lc As Long
    Dim lr As Long
    Dim ec As Long
    Dim er As Long
   
'   Find last column in row 1 with data
    lc = Range("A1").End(xlToRight).Column
   
'   Find last row in column A with data
    lr = Range("A1").End(xlDown).Row
   
'   Find ending column
    ec = Range("A1").SpecialCells(xlLastCell).Column
   
'   Find ending row
    er = Range("A1").SpecialCells(xlLastCell).Row
   
'   Delete extra columns
    If ec > lc Then
        Range(Cells(1, lc + 1), Cells(1, ec)).EntireColumn.Delete
    End If
   
'   Delete extra rows
    If er > lr Then
        Rows(lr + 1 & ":" & er).Delete
    End If
   
End Sub

 

[dougebowl]

Okay, so my data could have a blank column and then additional data. Same for the rows. So, I need something to define the last column and row and perform the delete functions. Sounds like this may not be able to be done as you state that I cannot have blank columns/rows and then additional data.

In an effort to be more clear, my dataset is A1 to D5 (of course this is dynamic based on the dataset I am working with). Any additional values outside of this range, has to be used to define the last Column/Row. Is there not a way to define the range based on A1 to the first blank Column/Row and then delete everything to the right/below the range?

 

[Joe4]

Sounds like this may not be able to be done as you state that I cannot have blank columns/rows and then additional data.

I think you misunderstood what I was saying. Though that is not what the original request implied, it can be done, as long as we know the conditions/limitations.

Is there not a way to define the range based on A1 to the first blank Column/Row and then delete everything to the right/below the range?

Yes, that is exactly what the revised code in my previous post does. Did you try it?

What I was saying is that it would not work if you have blank cells in column A in the middle of your data.
So, in your example, where your data set is A1:D5, if A3 were to be blank, that would be problematic using this method, as it would determine A2 is the last row going down column A.
But if you do not have any blank cells on any valid data row in column A (that you want to keep), then this won't be an issue.

 

[dougebowl]

Thanks for clarifying. I did try the revised code and the values I entered outside my dataset did not delete. So, I could only conclude that it did not work.

I entered a value in H6 and F10. After running the code, the values did not delete. Column E - G are blank, along with rows 6 - 9 are blank.

 

[Joe4]

There must be something going on with your sheet. Do you have formulas returning blanks, or have you pre-formatted certain unused ranges?

Run this version and tell me exactly what the MsgBox at the end returns:

Sub DeleteRC()

    Dim lc As Long
    Dim lr As Long
    Dim ec As Long
    Dim er As Long
   
    ActiveWorkbook.Save
    
'   Find last column in row 1 with data
    lc = Range("A1").End(xlToRight).Column
   
'   Find last row in column A with data
    lr = Range("A1").End(xlDown).Row
   
'   Find ending column
    ec = Range("A1").SpecialCells(xlLastCell).Column
   
'   Find ending row
    er = Range("A1").SpecialCells(xlLastCell).Row
   
'   Delete extra columns
    If ec > lc Then
        Range(Cells(1, lc + 1), Cells(1, ec)).EntireColumn.Delete
    End If
   
'   Delete extra rows
    If er > lr Then
        Rows(lr + 1 & ":" & er).Delete
    End If
   
    MsgBox "lc = " & lc & vbCrLf & _
            "lr = " & lr & vbCrLf & _
            "ec = " & ec & vbCrLf & _
            "er = " & er
            
End Sub