?
Solved

deleting rows from excel

Posted on 2006-07-19
11
Medium Priority
?
200 Views
Last Modified: 2010-05-03
Hi,
I have a large excel sheet, with some blank rows in different locations, i want to delete all of these, is this possible?

thanks
0
Comment
Question by:markmchugh
10 Comments
 
LVL 4

Expert Comment

by:BradB3030
ID: 17141674
Yes, you can use the Excel.Application object, you can loop through each row, check to see if there's information in specific cells...if not, you can delete it.

Example at home if you need it...
0
 

Author Comment

by:markmchugh
ID: 17141685
please, can you post an example?
0
 

Author Comment

by:markmchugh
ID: 17143264
got a solution.................. no need for anybody to post
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 

Author Comment

by:markmchugh
ID: 17143294
sorry, the solution does not work.... i've increased the points available
0
 

Author Comment

by:markmchugh
ID: 17143479
ok, here is the solution i used, i used the following excel macro

Sub DeleteBlankRows1()
'Deletes the entire row within the selection if the ENTIRE row contains no data.

'We use Long in case they have over 32,767 rows selected.
Dim i As Long

      'We turn off calculation and screenupdating to speed up the macro.
      With Application
            .Calculation = xlCalculationManual
            .ScreenUpdating = False
     
      'We work backwards because we are deleting rows.
      For i = Selection.Rows.Count To 1 Step -1
            If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then
                  Selection.Rows(i).EntireRow.Delete
            End If
      Next i

            .Calculation = xlCalculationAutomatic
            .ScreenUpdating = True
       End With
End Sub


to tun it

Open Excel.
Push Alt+F11 to open the VBE (Visual Basic Editor).
Go to Insert>Module.
Copy the code and paste it in the new module.
Push Alt+Q to return to Excels normal view.
Push Alt+F8 and then select the macro name and click Run. Or select Options and assign a shortcut key.


i got this from http://www.ozgrid.com/VBA/VBACode.htm

no need to comment further

thanks
0
 
LVL 1

Expert Comment

by:Youkko
ID: 17184301
So, if the solution is already found, what about closing this topic?
0
 

Author Comment

by:markmchugh
ID: 17195892
how do i close?
0
 
LVL 1

Expert Comment

by:Youkko
ID: 17195919
I believe if you accept your own answer would do...
0
 
LVL 1

Expert Comment

by:Youkko
ID: 17195923
If not, ask a moderator..
0
 

Accepted Solution

by:
CetusMOD earned 0 total points
ID: 17371152
PAQed with points refunded (500)

CetusMOD
Community Support Moderator
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

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

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month6 days, 8 hours left to enroll

593 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