Macro to delete all rows based on one condition

[vemix]

Hi guys,

In my spreadsheet I have over 600K rows, and some of them are not required i.e. they are errors.
They are not all blank, so I cannot use macro for deleting blank rows.

I would like to use a macro that deletes all rows from the spreadsheet that contain "#VALUE!" in J column. I'd appreciate very much!

Thanks,
vemix

[arlu1201]

Does the formula in the cell result in #VALUE or there are no formulae but just #Value in the cell?

[Trebor76]

Hi vemix,

Try this (though initially on a copy of your data as the results cannot be undone if they're wrong):

Please Login or Register  to view this content.

Regards,

Robert

[vemix]

Hi guys,

Thanks for your posts. The data is simply a text, and not a formula error.

Trebor76 - I tried your macro, and I believe I need some adjustments there right ? My knowledge to using macros is limited to clicking "Record macro", or sometimes changing sheet names in the macro but that's it. I'd appreciate some more guidance :-)

[sonu_kumar444]

To delete empty rows in a selected range we can use the following macro. The macro here uses the For Next Loop. First the macro counts the rows in a selected range to determine the when the macro should stop. The For Next statement acts as a counter.

Sub DelEmptyRow()
Rng = Selection.Rows.Count
ActiveCell.Offset(0, 0).Select
Application.ScreenUpdating = False
For i = 1 To Rng
If ActiveCell.Value = "" Then 'You can replace "" with 0 to delete rows with 'the value zero
Selection.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
Next i
Application.ScreenUpdating = True
End Sub

[arlu1201]

Sonu,

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

[Trebor76]

Trebor76 - I tried your macro, and I believe I need some adjustments there right?

Only to the wstMyTab and the lngStartRow variables I've commented in the code.