Clear Cells in Last Row

[Chris Lowe]

I have a table where sometimes I need to delete everything in the last row, the table has columns from B to CP1.

I have managed to find the last row with vba but have so far only been able to select the cells one column at a time, is there a way that I can select the entire row of cells with data?
I used this code to select one at a time.

Range("B" & Rows.Count).End(xlUp).Select

I'm still very much learning all this stuff.

 

[Michael M]

maybe...

Sub MM1()
Dim lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
With Rows(lr)
    .MergeCells = False
    .ClearContents
End With
End Sub

 

[Chris Lowe]

I have cells merged both horizontally & vertically, for example B27:E28 is a block of merged cells.

Based on what you have said I may go back & start again without the merged cells.

 

[Rick Rothstein]

I have cells merged both horizontally & vertically, for example B27:E28 is a block of merged cells.

Based on what you have said I may go back & start again without the merged cells.

I think you better... the problem with vertically merged cells, especially if all columns are not merged for the same number of rows, is determining what the last "row" is.

 

[Michael M]

did you try my code ?
Also, why are the cells merged...?
you can achieve the same thing with
higlight the cells>>right click>>format>>alignment>>horizontal>>center across selection>

 

[My Aswer Is This]

Try this:

Sub ClearLastRow()
Rows(ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row).ClearContents
End Sub

 

[Rick Rothstein]

you can achieve the same thing with
higlight the cells>>right click>>format>>alignment>>horizontal>>center across selection>

I don't think you can do that with multiple rows in the selection (only the first selected row would be used).

 

[Michael M]

true...I was working on horizontal cells...not multiple rows.
Either way, I think you comment in Post #13 to be most relative.

 

[Michael M]

@my aswer is this.
Still wont do the desired effect with Merged cells

 

[My Aswer Is This]

Rick, I could not get your solution to work. Your solution looks for the last row in column 'B" what if column "G" is longer

I don't think you can do that with multiple rows in the selection (only the first selected row would be used).

 

[Rick Rothstein]

Rick, I could not get your solution to work. Your solution looks for the last row in column 'B" what if column "G" is longer

I used Column B because the OP did so in Message #1. A more generic LastRow finder for a given set of columns would be...

LastRow = Columns("B:CP").Find("*", , xlValues, , xlRows, xlPrevious).Row