Solved

Delete all rows below and including the current cell

Posted on 2013-01-21
3
1,756 Views
Last Modified: 2013-01-21
I need some VBA code that will delete all rows below and including the current cell. Some of the cells below will contain data some will be blank. I need the code to clear all data below and including the current cell.

Thanks
0
Comment
Question by:Jagwarman
3 Comments
 
LVL 1

Expert Comment

by:ttornqvist
ID: 38800795
Hi,

Would this work in your case:

Sub DeleteAllBelow()
Row = ActiveCell.Row
    Rows("" & Row & ":1048576").Select
    Selection.Delete Shift:=xlUp
End Sub

Open in new window


Change the number of rows to 65536 if you are working with Excel 2003 or older (*.xls files):

Sub DeleteAllBelow()
Row = ActiveCell.Row
    Rows("" & Row & ":65536").Select
    Selection.Delete Shift:=xlUp
End Sub

Open in new window

0
 
LVL 24

Accepted Solution

by:
Steve earned 500 total points
ID: 38800881
This code should do it in one line and work in all excel versions:

Sub DeleteAllBelow()
    Rows(ActiveCell.Row & ":" & Rows.Count).Delete Shift:=xlUp
End Sub

Open in new window


If you just want to clear the cells (leave formatting alone etc)...
Sub DeleteAllBelow()
    Rows(ActiveCell.Row & ":" & Rows.Count).ClearContents
End Sub

Open in new window

0
 

Author Comment

by:Jagwarman
ID: 38801052
Yes that works for me.

Thanks
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Outlook Free & Paid Tools
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

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