Solved

find last row  in a column and clear contents.

Posted on 2011-02-28
11
226 Views
Last Modified: 2012-05-11
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
Comment
Question by:allwork32
11 Comments
 
LVL 30

Assisted Solution

by:SiddharthRout
SiddharthRout earned 225 total points
ID: 35003184
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
 
LVL 10

Assisted Solution

by:Makrini
Makrini earned 25 total points
ID: 35003192
Sub tester()
    Dim lastrow As Long
    lastrow = Range("B1000000").End(xlUp).Row
    Rows(lastrow + 1 & ":1000000").ClearContents
End Sub

Open in new window

0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35003201
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 10

Expert Comment

by:Makrini
ID: 35003204
agreed - mine was a quick dirty solution, didn't see you had replied till I hit send
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35003206
That's ok :) No harm done :)

Sid
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 225 total points
ID: 35003216
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35003218
@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
 
LVL 10

Expert Comment

by:Makrini
ID: 35003245
:)  - good work
0
 
LVL 1

Author Comment

by:allwork32
ID: 35003330
thank you everyone, i'll check it out when I return to work in the morning.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35003759
@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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35003770
Yes. I missed that in the 2nd code. I did incorporate that in the 1st one though :)

Sid
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This collection of functions covers all the normal rounding methods of just about any numeric value.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question