Reading Blank Data (English)

Topic

New Reply

How would I write a macro so that when it is checking the cells data, it will be able to read that the cell has no data and will delete the entire row?

Reply | Quote

Replies

Ximon

Would that help? This would look for cells in Column A all the way down to the last row of your worksheet and if that cell is empty then it deletes the row.

Sub DeleteBlankRows()
Dim lCounter As Long

With ActiveSheet
For lCounter = 2 To .Rows.Count
If Len(CStr(Range(“A” & lCounter).Value)) = 0 Then
.Rows(lCounter).Select
‘/Cell is blanck so delete row.
.Rows(lCounter).Delete Shift:=xlUp
End If
Next lCounter
End With
End Sub

Wassim

Reply | Quote

Thanks for such a quick reply.

Reply | Quote

Hi Wassim,

I tried the macro you wrote for me and it works very good, but there is one thing. When I delete the row I notice that the data from the previous row has moved up a row and so I don’t think I should be deleting entire rows. How would I only delete the data that is in the cells of colume B and C of the selected rowusing your example?

Reply | Quote

I am not clear on what you want to do.

It sounds like you want to delete the data from the cell but if there is no data in the cell, there is nothing there to delete.

If a cell in Column B is empty, do you want to delete the cell and move the rest of the cells in column B up without affecting data that might be in other columns? If the cell in B is empty, do you want to delete the cell in column C even if it contains data, or do you want to only delete cells in column C if they are empty. What do you want to do if one of the cells is empty but the other is not?

Reply | Quote

Here is the situation. I have the two formulas in all the cells in column A and B. They both reference to the cells in column C. I want to delete the formulas in the cells of columns A and B that have cells in column C with no data. Is this possible? or am I just confusing you?

Reply | Quote

You could set your formulas up to return a blank if the contents of C is blank,

    =IF(Cx = "","",Formula),

where x is the row number, and Formula is your original formula (without the = sign).

Would that help ?

Andrew C

Reply | Quote

Ok, I think I am beginning to understand what you want to do. However, one more question before we proceed. Are you really just trying to keep a zero or error from displaying in columns A and B if C is empty, or do you really need to delete the formulas? You can keep the zero or error from displaying by using a simple formula, and then you don’t have to reenter the formula if you later enter something in C. If the formula in A is =1/C1, which will produce an error is C is empty, then you can change the formula to:

=IF(C1="","",1/C1)

Reply | Quote

I really need to delete the formula. I have to have the cells in column A and B to to empty.

Reply | Quote

Sub ClearUp()
Dim rngCell As Range
For Each rngCell In Columns(“C:C”).SpecialCells(xlCellTypeBlanks)
rngCell.Offset(0, -1).ClearContents
rngCell.Offset(0, -2).ClearContents
Next rngCell
End Sub

Reply | Quote

Hi John,

I used your program and it worked perfectly. Just to bother you some more, if I wanted it look for cells with #N/A instead of the blank cell, how would I do that?

Reply | Quote

You would use similar construction, except you have to check on the #N/A status cell by cell:

For Each rngCell In Selection.SpecialCells(xlCellTypeFormulas, xlErrors)
If rngCell.Value = CVErr(xlErrNA) Then

End If
Next rngCell

Do you want a routine that only looks for functions that evaluate to #N/A and deletes them, or do you want a routine that includes the deletion of cells in columns A & B routine we already have, and if so do you want the #N/A formula cell in column C also deleted?

Reply | Quote

I see that John has already posted a solution that looks like it does what you want.

Reply | Quote

Ximon

There is a better solution, faster and easier:

OK the formula you would use is like this: =IF(C1=””,1/0,C1) You would fill this formula down to all the rows with data.

Now this formula will produce an error (#DIV/0!) where cell in C is empty.

Then you would need to delete these formulas:
Select columns A and B and Press F5 to activate the GoTo
Then select special
From the dialog select Formulas and uncheck all but Errors.

This will select all the cells with errors and then you simply hit the delete key and you are done.

Now if you need to do this many times, you would need to possibly automate it.

As Legare had mentioned, my code will miss some rows, so you would need to run it twice or even more. The use of a For-Next loop in this case is not the best way of doing things.

OK hope all of us have given you some starting points to get to your optimal solution.

Wassim

Reply | Quote

Thanks everyone for all your help.

Reply | Quote

Ximon

Try this version then:

Sub DeleteBlankCells()
Dim lCounter As Long

With ActiveSheet
For lCounter = 2 To .Rows.Count
If Len(CStr(Range(“A” & lCounter).Value)) = 0 Then
‘/Cell is blanck so delete row.
.Range(“B” & lCounter).Resize(1, 2).Delete Shift:=xlUp
End If
Next lCounter
End With
End Sub

I also used the resize option here just to give you an example of how you can resize a range that you pick. But I am afraid that anytime you delete rows like that, the data may become skewed, that is the rows don’t match. I don’t know what the data you are working with looks like, but I need to caution you on this issue.

Wassim

Reply | Quote

Thanks I’ll try it right away.

Reply | Quote

I understood you didn’t want the row deleted; if so you might try my contribution. Its only danger is that it will delete anything in columns A & B including text and headings, etc., if the cell in the corresponding row in column C is blank.

Reply | Quote

Your routine will miss deleting rows if there are two in a row with empty cells.

Reply | Quote

Legare

You are right, I should not use a For-Next loop for this one. I was not sure on what the requierments were so i decided to provide a starting point. I guess I need to send another reply because a formula and a sort will be better and faster…

Thanks

Wassim

Reply | Quote

A For Next loop works great. The trick is that you have to start at the end and work backward.

Reply | Quote