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.
 
For Gary Hewitt-Long,

To try and help Roni on, I've had a look at the spreadsheet in question.

What he needs is for the macro to test each row, along the range C4:L4 to see if any data is present. If nothing, then delete the row. If any data is present in any cell then the row remains. The macro then needs to carry on testing up to C290:L290

I've amended your VB as follows and run it:
Sub deleteBlankRows()

Range("C4:L4").Select ' Select the starting cell
For i = 4 To 290 ' 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("C290:L290").Select

End Sub

Whilst it has deleted approx 185 blank rows, it has still left some 90 rows which I know have no data in them.

Knowing nothing about VB, I'm stuck - can you dig me out of it?

Cheers,

Nobby
 
Upvote 0

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Re the above posting, I've run another couple of tests on the code and it has also deleted rows where data definitely exists.

Could it be anything to do with the use of "" for testing for a blank cell i.e. is this looking for no text rather than no data at all? Dunno, just guessing

Nobby
 
Upvote 0
I've used the last code and it's not working.
The first two columns have TEXT in each row, but I only want the macro to look at columns C-L for NUMBERS, if no numbers, delete ENTIRE row (including TEXT in first two columns).

Also, it should ignore the formula in Column M for each row (adding across each row). There are "0.00" in each box if there isn't a number in columns C-L. I don't want those 0.00's to count as NUMBERS in the macro.

HELP. Thanks again.
 
Upvote 0
ACtually, my last post about trying the prior code was in reference to the code prior to that ( hadn't seen the last one yet). But it sounds like that isn't working either. HELP some more.

Thanks
 
Upvote 0
Hello, I'm assuming your data is in the range C4:L104. If so, you may want to try the following macro. Just paste it as described earlier and run it.

Sub Crunch()
myrng = ActiveCell.Address
Application.ScreenUpdating = False
[m4] = "=sum(l4:c4)"
[m4].Copy
[m4:m104].Select
ActiveSheet.Paste
Application.CutCopyMode = False
For Each cell In Selection
If cell.Value = "0" Then
cell.ClearContents
End If
Next cell
Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Selection.ClearContents
Range(myrng).Select
Application.ScreenUpdating = True
End Sub

HTH. Cheers, Nate
This message was edited by NateO on 2002-02-28 10:26
 
Upvote 0
Almost perfect (and thank you), however
when I run the macro, I get an error screen "Run time error "1004" Cannot change part of merged cell" to which I click END and then OK to same "merged cell" error message. I searched thru my spreadsheet, I can't find any merged cells. Any ideas.

Also, sometimes (and I haven't found a pattern yet) it doesn't ALWAYS delete all the blank rows. But if I go back and change the row number to new row number and run it again, it takes out the remaining empty rows???)
 
Upvote 0
On 2002-02-28 10:59, Anonymous wrote:
Almost perfect (and thank you), however
when I run the macro, I get an error screen "Run time error "1004" Cannot change part of merged cell" to which I click END and then OK to same "merged cell" error message. I searched thru my spreadsheet, I can't find any merged cells. Any ideas.

Also, sometimes (and I haven't found a pattern yet) it doesn't ALWAYS delete all the blank rows. But if I go back and change the row number to new row number and run it again, it takes out the remaining empty rows???)

If you shouldn't have ANY merged cells in the spreadheet, select the whole sheet then right click, select Format cells, Alignment and uncheck Merge Cells.

The code I put up did check for "" i.e. nothing there and iterated through column A from the starting point you tell it, untill it has iterated the number of times you put in the code.

Someone mentioned it left cells undeleted, yep it must have done looking at it again, sorry.

The reason it did that was that when it deleted the row it shifted the cells up and THEN moved down a row. Trouble is the cell it moved into imeadiately after deleting the row was the one it should have been testing next, therefore it didn't get tested. Whoops, sorry.

The alternative would be to use xlDown (but I haven't tested it! :eek:)
 
Upvote 0
Come on Gareth,

this post was over 2 months old till you dredged it back up to the top of the list !!!!

Your suggestion of "autofilter" had already been suggested by Jack anyhow, 2 months ago

Full marks for looking through older entries though, it's amazing what gems are hidden away in the archives :)

Chris
:)
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

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