Deleting rows not containing data. HOW?

I have a spreadsheet with 10 columns, the first of which contains TEXT, the other 9 to contain data if necessary, and 100 rows. The rows add across on each line and the columns add down.

I want Excel to delete the rows that have NO digits in them. Would this be some kind of Formatting command?

Help!!!

Feel free to email me directly at
rgardner@mdp.com

and thank you.

[Jack in the UK]

Hi ==

Can you not autofilter and filter to BLANKS and delete the selection???

This will do it.

I dunno.
Can you explain Autofilter and Filter to Blanks to a novice Excel user? Please!

Thank you

[gplhl]

On 2002-02-27 13:12, Anonymous wrote:
I have a spreadsheet with 10 columns, the first of which contains TEXT, the other 9 to contain data if necessary, and 100 rows. The rows add across on each line and the columns add down.

I want Excel to delete the rows that have NO digits in them. Would this be some kind of Formatting command?

Help!!!

Feel free to email me directly at
rgardner@mdp.com

and thank you.

Sub deleteBlankRows()

Range("A1").Select ' Select the starting cell
For i = 1 To 100 ' Change 100 to whatever the last row you want to iterate to.

If ActiveCell.Value Like 0 Then
ActiveCell.EntireRow.Select
Selection.Delete Shift:=xlUp
ActiveCell.Offset(RowOffset:=1).Activate

Else

ActiveCell.Offset(RowOffset:=1).Select

End If


End Sub



_________________
Regards,

Gary Hewitt-Long

[ This Message was edited by: gplhl on 2002-02-27 13:47 ]

totally appreciated...however, still a little too complex for me. where do I type (?) all that???

[gplhl]

On 2002-02-27 13:57, Anonymous wrote:
totally appreciated...however, still a little too complex for me. where do I type (?) all that???

When you have your workook open, press Alt + F11.

This will open your VBA editor.

Right click on ThisWorkbook for the project of the workbook you want this macro in. Click on Add.. Module.

Paste the code in the module, save and then you can run the macro from tools Macro.

[Chris Davison]

Apologies to Gary if your VBA already interprets this and takes care of it...

Anon :

by "digits" do you just mean the numbers 1-9 or do you mean "anything" ie any numbers or characters, letters, signs etc etc ?

[gplhl]

That'l teach me not to check code when I write it. The last one didn't work, I forgot to put Next i and used 0 (which I use in one of my own macros) instead of "" to look for empty cells.

Sorry

Working code below.


Sub deleteBlankRows()

Range("A1").Select ' Select the starting cell
For i = 1 To 100 ' Change 100 to whatever the last row you want to iterate to.

If ActiveCell.Value Like "" Then
ActiveCell.EntireRow.Select
Selection.Delete Shift:=xlUp
ActiveCell.Offset(RowOffset:=1).Activate

Else

ActiveCell.Offset(RowOffset:=1).Select

End If

Next i
Range("A1").Select

End Sub

I mean only NUMBERS. There will be text on each row, but if there aren't any numbers in any of the columns, I want the row deleted.

Is that what that VB mocule will do???

Also, will GPLHL's code work as is if I don't want the first two columns included? (They both have text in them.) The next 8 have or potentially could have NUMBERS (which if they don't, I want the entire row deleted), followed by a last column with a formula to add across each row. (This formula will not interfere with the code, will it?)

Thanks for all your help, so far.