?
Solved

Delete all rows below and including the current cell

Posted on 2013-01-21
3
Medium Priority
?
2,241 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 2000 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

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

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

Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

752 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