Solved

deleting rows from excel

Posted on 2006-07-19
11
189 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
11 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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VBA to copy paste columns form one file to other 20 85
MS Access Search and Replace Using VBA 6 57
message box in access 4 41
vb6 - Transfer from MSHFlexgrid1 to xls issue 8 47
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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 process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

920 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

16 Experts available now in Live!

Get 1:1 Help Now