Solved

Delete rows in excel

Posted on 2010-08-25
19
322 Views
Last Modified: 2012-05-10
Hello,

I am new to Excel and am trying to create a Macro to delete empty rows.  For example, I am trying to delete an entire row if nothing is populated in certain cells within that row (i.e. if nothing is in Column E).

I have attached a very simplified version of what I am trying to do.  I have data in rows 2, 5, and 6, but now want to run a macro that will delete rows 3 and 4 (especially when I have hundreds of lines of data).  

Please provide very basic instructions starting with how to set-up the Macro / Run the Macro.

Thank you,
Megan  

Macro-Question.xlsx
0
Comment
Question by:mweiner
  • 8
  • 7
  • 2
  • +1
19 Comments
 
LVL 1

Expert Comment

by:PsychoRerr
ID: 33526219
You probably want to use the Goto functionality. One of the things it can select for you is all of the empty rows in a sheet.
0
 

Author Comment

by:mweiner
ID: 33526239
I don't know how to do that.  I need very basic instructions as to how to use that funcionality.
0
 
LVL 18

Expert Comment

by:Anil Golamari
ID: 33526262
http://www.cpearson.com/excel/deleting.htm ( sample code).


Public Sub DeleteBlankRows()
Dim dbMaxRow As Double, dbMinRow As Double, i As Double
Dim dbMaxCol As Double
Dim rng As Range

On Error Resume Next

'only look in used area of the worksheet where active cell is
Set rng = Selection.Parent.UsedRange

'calculate area to be searched for blank rows
dbMaxRow = rng.Rows.Count '# of rows in used area
dbMinRow = rng.Cells(1, 1).Row '1st row in used area
dbMaxCol = rng.EntireColumn.Count '# of columns in used area

For i = dbMaxRow To dbMinRow Step -1
If IsError(rng.Cells(1, 1).Offset(i - 1, 0).EntireRow. _
SpecialCells(xlCellTypeBlanks).Count) Then
Else
If rng.Cells(1, 1).Offset(i - 1, 0).EntireRow. _
SpecialCells(xlCellTypeBlanks).Count = dbMaxCol Then
rng.Cells(1, 1).Offset(i - 1, 0).EntireRow.Delete
End If
End If
Next i

Set rng = Nothing

End Sub


hope these help you.
0
 

Author Comment

by:mweiner
ID: 33526288
Thank you!  Do your solutions work if there are formulas in the cell that return a zero value?
0
 
LVL 1

Expert Comment

by:PsychoRerr
ID: 33526326
Actually, I take that back. 2003, at least, will only select blank cells.

Check out: http://www.ozgrid.com/VBA/VBACode.htm
If all of your rows are either empty or complete (i.e. no partial ones), as in your example, the code for DeleteBlankRows2 they give should be sufficient and most efficient.
0
 
LVL 18

Expert Comment

by:Anil Golamari
ID: 33526335
0
 

Author Comment

by:mweiner
ID: 33526636
I apologize - these solutions are still too complex for me.  Is there anyway that someone could apply the solution to my example?  I have tried to include the codes in my excel spreadsheet but am not getting anywhere and am confused as to what is the code vs. what should not be included.  Please make this solution as simple as possible for me as I am very new to Macros.  Thank you again.
0
 
LVL 13

Expert Comment

by:Nick Denny
ID: 33526879
This is simple enough to do if there is "commonality" in a column .
For example  - for each row, if there is nothing in column A and if the value of A is zero - then delete the row - you could use code below.

To explain:
Application.ScreenUpdating = False, meaning the screen does not flicker as it performs the code.
The code will check the "used range" on sheet 1.
If, on each row, there is nothing in the first cell (column A) or the cell value is zero, thn the row will be added to "Trange".
Once the code has checked all of the "used range", it will then delete all the rows in "trange" in one go.

Cheers

Nick
Option Explicit



Sub Delete_rows()



Dim Trange As Range

Dim Row As Range



Application.ScreenUpdating = False



For Each Row In Sheets(1).UsedRange.EntireRow.Rows

    If Row.Cells(1, "A") = "" Or Row.Cells(1, "A") = 0 Then

        If Trange Is Nothing Then

            Set Trange = Row

        Else

            Set Trange = Union(Trange, Row)

        End If

    End If

Next Row

    If Not Trange Is Nothing Then

        Trange.Delete Shift:=xlUp

    End If

   

Application.ScreenUpdating = True



End Sub

Open in new window

0
 
LVL 13

Expert Comment

by:Nick Denny
ID: 33526898
Here is your file with code included.
Copy-of-Macro-Question.xlsm
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 13

Accepted Solution

by:
Nick Denny earned 500 total points
ID: 33526937
Sorry - also see below for how to add and use code:

On a sheet tab, right click, then click "view code" - this opens the VBA editor.
(Or hit Alt + F11).
On the menu bar at the top, click on insert > module.
This opens a module in the Active workbook.
(If you want to add code to another workbook, select the file from the project explorer on the left of the VB editor.
(If the project explorer is not showing, hit Ctrl + R).

Copy and paste the whole code snippet supplied, adapting anything to suit.
(Or write your own code)
Save the workbook.

You may need to enable macros.
http://office.microsoft.com/en-us/excel/HA100310711033.aspx#12

To run the macro there are a few choices:
http://office.microsoft.com/en-us/excel/HP100141131033.aspx

To add to your personal.xls(b), and thus have the code available at all times:
http://www.rondebruin.nl/personal.htm
0
 

Author Comment

by:mweiner
ID: 33526955
Seriousnick - this has been SO helpful.  Thank you so much - my question has been answered.
0
 
LVL 13

Expert Comment

by:Nick Denny
ID: 33527005
Many thanks for grade and points.
Glad to have helped  :)
0
 

Author Comment

by:mweiner
ID: 33530902
Seriousnick - I have tried to apply the formula to my spreadsheet and am having trouble b/c the name of the Sheet in the formula you constructed for me is not the same as the name of the Sheet in my Excel document (my sheet's name is "LP Template").  How do I change your code to recognize this?  Please let me know your thoughts.  Thanks so much.  
0
 

Author Comment

by:mweiner
ID: 33531105
I figured out my above question, but now the Macro is deleting everything in my spreadsheet, including rows where I have title cells.  How do I adjust the formula to apply my macro to specific rows only (i.e. Rows 13 - 40)?  THANK YOU.
0
 
LVL 13

Expert Comment

by:Nick Denny
ID: 33531154
In VBA you can reference a sheet in 3 ways.

1, By Sheet Name (the name you have given it)
2, By Sheet Index (its position in the workbook)
3, By Sheet Code Name (its code name/number as viewed in VB editor - generally the best choice as this always remains the same even if the sheet is renamed or moved)

In the code supplied above, I have used method 1 - that is to say "sheets(1)" refers to the first sheet in your workbook.

The easiest way to change to suit, would be to change this line:

For Each Row In Sheets(1).UsedRange.EntireRow.Rows

to

For Each Row In Sheets("LP Template").UsedRange.EntireRow.Rows

However, the best way, would be to look in VB Editor (hit ALT + F11), and under your workbook, you will see "microsoft excel objects" and under that "sheetx("LP Template")".
x being a number.

Sheetx woud be the code name number. So you would now have:
For Each Row In Sheetx.UsedRange.EntireRow.Rows

So even if you moved or renamed this sheet, the code would still know which sheet to work on.

Hope this makes sense.

Cheers

Nick

0
 
LVL 13

Expert Comment

by:Nick Denny
ID: 33531216
I actually used option 2 above and not option 1.

Please see below to confine the code to the reqired rows.
Option Explicit



Sub Delete_rows()



Dim Trange As Range

Dim Row As Range



Application.ScreenUpdating = False



For Each Row In Sheets("LP Template").Range("A13:A40").EntireRow.Rows

    If Row.Cells(1, "A") = "" Or Row.Cells(1, "A") = 0 Then

        If Trange Is Nothing Then

            Set Trange = Row

        Else

            Set Trange = Union(Trange, Row)

        End If

    End If

Next Row

    If Not Trange Is Nothing Then

        Trange.Delete Shift:=xlUp

    End If

   

Application.ScreenUpdating = True



End Sub

Open in new window

0
 
LVL 13

Expert Comment

by:Nick Denny
ID: 33531355
It would also follow on if you wanted a different column to be checked for content, for example column E, you would then change the line:

If Row.Cells(1, "A") = "" Or Row.Cells(1, "A") = 0 Then

to

If Row.Cells(1, "E") = "" Or Row.Cells(1, "E") = 0 Then


Or if you JUST wanted to check for blank cells:

If Row.Cells(1, "A") = "" Then

etc.

Cheers

Nick
0
 

Author Comment

by:mweiner
ID: 33531520
Nick -

This has been tremendously helpful.  Last question...  Can I somehow create a button on my spreadsheet that says "Finalize LP" and when I select that button the Macro runs?  Is that possible?

Thank you again,
Megan
0
 
LVL 13

Expert Comment

by:Nick Denny
ID: 33532271
Yes.
If developer tab is not showing, go to Excel Options (from MS button at top) > Popular > check the box for "Show Developer Tab In Ribbon".
Insert a button, using the "insert" > top left shape.
Draw a shape for the button.
Assign the macro from the box that will open when you have drawn the shape.
Adjust the text to reas as you want it.

There is a simple to follow video on youtube:

http://www.youtube.com/watch?v=cbDbA83DbhI

Cheers

Nick
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now