Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 296
  • Last Modified:

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.
0
allwork32
Asked:
allwork32
3 Solutions
 
SiddharthRoutCommented:
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
 
MakriniCommented:
Sub tester()
    Dim lastrow As Long
    lastrow = Range("B1000000").End(xlUp).Row
    Rows(lastrow + 1 & ":1000000").ClearContents
End Sub

Open in new window

0
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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:
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
 
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now