Deleting rows not containing data. HOW?

G

Guest

Guest
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.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
I dunno.
Can you explain Autofilter and Filter to Blanks to a novice Excel user? Please!

Thank you
 
Upvote 0
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
 
Upvote 0
totally appreciated...however, still a little too complex for me. where do I type (?) all that???
 
Upvote 0
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.
 
Upvote 0
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 ?
 
Upvote 0
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
 
Upvote 0
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???
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top