Deleting empty lines using a macro

[Dorocica]

Hello,

I have another report which needs to be sorted using a macro. However, before I can sort the data, I need to delete rows at the bottom of each report that have "0" values in column A and B. Again, each report I run is of different length so I need to write a code that will take that into consideration. Here is an example of what I need to do:

Sub second_step()
'
' second_step Macro
'

'
Range("C1").Select
Range(Selection, Selection.End(xlDown)).Select
Rows("7876:7876").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("A1").Select
End Sub

Now, the thing is, not every report will start having "0" values in row 7876. How do I write a code to reflect this, please?

[mikeTRON]

Do you want the code to treat a blank cell as a zero or as a blank?

This will not clear the row if either column A or B is blank:
If Range("A" & i).Formula = 0 And Range("B" & i).Formula = 0 Then
This will clear the row if either is column A or B blank:
If Range("A" & i).Value = 0 And Range("B" & i).Value = 0 Then


The entire code:

Please Login or Register  to view this content.

Here is the file I created while creating this:
ClearRowContents.xlsm

[Dorocica]

Hi,

Many thanks for your reply but this didn't work ...

I have some data in columns A-G. Columns A and B then go on to have some zeros in the cells at the bottom of each report. I need to get rid of those "0" from column A and B from the bottom of my report as I then need to sort it.

I really don't understand why the command Ctrl+Shift+arrow down doesn't work???

Thanks!

[Dorocica]

Would an IF THEN command work for this?

Something like IF there is a "0" in column A and B, THEN delete the entire row...

???

[JOHN H. DAVIS]

Does this help?

Please Login or Register  to view this content.

[berlan]

Try this:

Please Login or Register  to view this content.

Cheers,
berlan

[Dorocica]

Try this:

Please Login or Register  to view this content.

Cheers,
berlan

Hi,

Thank you. It worked only on column A. When I tried running the same code on column B it didn't work....

[mikeTRON]

Post a sample data set with a proposed solution so we can SEE exactly what you are trying to do, because there is a disconnect between what you are saying and what you actually need, apparently.

[mikeTRON]

Would an IF THEN command work for this?

Something like IF there is a "0" in column A and B, THEN delete the entire row...

???

This is what I had in the code listed above, but I think you were unclear on your expectations. In the sample code you had CLEAR the row, not delete.
Again, it helps a ton to have sample data and sample proposed solution so we can see exactly what you need.

[Dorocica]

Hello,

And sorry. I suppose my lack of knowledge about excel/macros is quite transparent.

I have attached a file that basically shows what I am trying to do. I have data in columns A to G and then "0" going quite a way down in column A and B.

I would like to have a macro that gets rid of all those cells with "0" value.

Thank you!