• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3035
  • 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
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

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