Clear Cells in Last Row

C

Chris Lowe

New Member
Joined
Mar 25, 2015
Messages
28
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.

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

I'm still very much learning all this stuff.
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Sort by date Sort by votes
try
Code: 
Sub MM1()
Dim lr As Long
lr = Cells(Rows.Count, "B").End(xlUp).Row
Rows(lr).Delete
End Sub
 
Upvote 0
try
Code: 
Sub MM1()
Dim lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
Rows(lr).ClearContents
End Sub
 
Upvote 0
Try this macro...

Code: 
Sub ClearLastRow()
  Cells(Rows.Count, "B").End(xlUp).EntireRow.ClearContents
End Sub
 
Upvote 0
That one didn't do anything.

I have this that works but is rather long winded

Code: 
Range("B" & Rows.Count).End(xlUp).Select
    Selection.ClearContents
    
    Range("F" & Rows.Count).End(xlUp).Select
    Selection.ClearContents
    
    Range("K" & Rows.Count).End(xlUp).Select
    Selection.ClearContents
        
    Range("O" & Rows.Count).End(xlUp).Select
    Selection.ClearContents
    
    Range("S" & Rows.Count).End(xlUp).Select
    Selection.ClearContents
    
    Range("W" & Rows.Count).End(xlUp).Select
    Selection.ClearContents
        
    Range("AA" & Rows.Count).End(xlUp).Select
    Selection.ClearContents
    
    Range("AE" & Rows.Count).End(xlUp).Select
    Selection.ClearContents
    
    Range("AI" & Rows.Count).End(xlUp).Select
    Selection.ClearContents
    
    Range("AN" & Rows.Count).End(xlUp).Select
    Selection.ClearContents
    
    Range("AR" & Rows.Count).End(xlUp).Select
    Selection.ClearContents
    
    Range("AW" & Rows.Count).End(xlUp).Select
    Selection.ClearContents
    
    Range("BB" & Rows.Count).End(xlUp).Select
    Selection.ClearContents
    
    Range("CH" & Rows.Count).End(xlUp).Select
    Selection.ClearContents
    
    Range("CP" & Rows.Count).End(xlUp).Select
    Selection.ClearContents
 
Upvote 0
Both mine and Ricks will do the same thing....but quicker and cleaner !!!
 
Upvote 0
Thanks chaps

Both nicely select the entire row, but won't clear the contents.
I get an error 1004 "cannot change part of a merged cell"
 
Upvote 0
you haven't helped by not telling us all the info...
Are the cells merged horizontally or vertically ?
And do they really need to be merged ??
worst thing to do in Excel is Merge Cells !!!
 
Upvote 0
Chris Lowe said:
Thanks chaps

Both nicely select the entire row, but won't clear the contents.
I get an error 1004 "cannot change part of a merged cell"
Click to expand...
You didn't tell us you had merged cells... they always cause problems; however, in this case, I believe the code I posted in Message #5 should still work... unless you merged the cells vertically. Did you try it and find that it did not work? If so, which cells are merged?
 
Upvote 0
You must log in or register to reply here.

Similar threads

CJG19
  • Solved
Dynamic Table Hide Rows
Replies
2
Views
127
CJG19
CJG19
D
  • Solved
Unable to select a cell in a worksheet
Replies
8
Views
210
rlv01
R
U
  • Solved
Copy/Paste Rows on select criteria
Replies
4
Views
811
UTB
U
C
  • Solved
Trouble with vba 'loop' finding last row
Replies
5
Views
327
CaptainGravyBum
C
T
  • Solved
Copy/Autofill formula to last cell
Replies
3
Views
42
Joe4
Joe4

Forum statistics

Threads
1,214,798
Messages
6,121,630
Members
449,041
Latest member
Postman24

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top