Solved

Delete all rows below and including the current cell

Posted on 2013-01-21
3
1,963 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

My experience with Windows 10 over a one year period and suggestions for smooth operation
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

730 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