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: 2596
  • Last Modified:

Delete all rows below and including the current cell

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
Jagwarman
Asked:
Jagwarman
1 Solution
 
ttornqvistCommented:
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
 
SteveCommented:
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
 
JagwarmanAuthor Commented:
Yes that works for me.

Thanks
0

Featured Post

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.

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