find last row in a column and clear contents.

experts help,

I'm looking for excel VBA code to find the last row in column "b" and clear the contents of each row after the the last row.
LVL 1
allwork32Asked:
Who is Participating?
 
SiddharthRoutConnect With a Mentor Commented:
Modified Code.

Sub tester()
    Dim lastrow As Long
    lastrow = Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Row
    Sheets("Sheet1").Rows(lastrow & ":" & Rows.Count).ClearContents
End Sub

Open in new window


Sid
0
 
SiddharthRoutConnect With a Mentor Commented:
Try this

Sub Sample()
    Dim LastRow As Long
    LastRow = Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Row + 1
    Sheets("Sheet1").Range("B" & LastRow & ":B" & Rows.Count).ClearContents
End Sub

Open in new window


Sid
0
 
MakriniConnect With a Mentor Commented:
Sub tester()
    Dim lastrow As Long
    lastrow = Range("B1000000").End(xlUp).Row
    Rows(lastrow + 1 & ":1000000").ClearContents
End Sub

Open in new window

0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
SiddharthRoutCommented:
Makrini: That won't work if the user has rows filled up till say B100001. Also the code will not work in Excel 2003 ;)

You should always avoid hard-coding rows :)

Sid
0
 
MakriniCommented:
agreed - mine was a quick dirty solution, didn't see you had replied till I hit send
0
 
SiddharthRoutCommented:
That's ok :) No harm done :)

Sid
0
 
SiddharthRoutCommented:
@allwork32: If you want to clear all rows then use the code in ID: 35003216 (Based on Markini's code) and if you want to clear all rows in Col B then use the code in ID: 35003184

Sid
      
0
 
MakriniCommented:
:)  - good work
0
 
allwork32Author Commented:
thank you everyone, i'll check it out when I return to work in the morning.
0
 
dlmilleCommented:
@Sid - would not the following be more correct?

    Sheets("Sheet1").Rows(lastrow+1 & ":" & Rows.Count).ClearContents

otherwise, lose that last row which has data?

dave
0
 
SiddharthRoutCommented:
Yes. I missed that in the 2nd code. I did incorporate that in the 1st one though :)

Sid
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.