Determining whether there is a blank Row 1

[Suggie]

I'm pretty new to VBA

I have a problem where I need excel to tell me whether there is a blank row or not. The code I am using is:

ActiveCell.FormulaR1C1 = _
&quot;=IF(OR(RC[4]<>0,RC[5]<>0,RC[6]<>0,RC[7]<>0,RC[8]<>0,RC[9]<>0,RC[10]<>0,RC[11]<>0,RC[12]<>0,RC[13]<>0),R2C5,&quot;&quot;Blank Row&quot;&quot&quot;

If one of the cells has data in it I want to keep the row, if not, the next code below deletes the rows that have been flaged as 'blank row' using the above formula

Dim Rng1 As Range
Dim Rng2 As Range

Set Rng1 = Intersect(Selection.Cells(1).EntireColumn, Selection.Parent.UsedRange)
Rng1.Replace &quot;Blank Row&quot;, &quot;&quot;, xlWhole
On Error Resume Next
Set Rng2 = Rng1.SpecialCells(xlCellTypeBlanks)
If Rng2 Is Nothing Then
'nothing to delete
Else
Rng2.EntireRow.Delete
End If

The problem is that the first formula is not flaging the rows that are completely blank. I think this may be a result of the cells containing data validation formulas, but the content are blank.

Has anyone any suggestions.

TIA,
Mark

 

[VBAjedi]

Try this line of code to test a row for actual contents:

RowNum = 5
RowIsEmpty = Worksheets("Sheet1").Rows(RowNum).Find("*") Is Nothing

RowIsEmpty will return True if the row is empty, false if not.

Let me know if this helps or if you need help getting it to work!

VBAjedi

 

[SkipVought]

Hi,

Ya gotta be careful about deleting rows. If I were doing it, I'd start from the botton and work up...

Sub ttt()
With ActiveSheet.UsedRange
  lFirstRow = .Row
  lLastRow = lFirstRow + .Rows.Count - 1
  iFirstCol = .Column
  iLastCol = iFirstCol + .Columns.Count - 1
End With

For lRow = lLastRow To lFirstRow Step -1
  bEmpty = True
  For iCol = iFirstCol To iLastCol
    If Not IsEmpty(Cells(lRow, iCol).Value) Then
       bEmpty = False
       Exit For
    End If
  Next
  If bEmpty Then Cells(lRow, iCol).EntireRow.Delete shift:=xlUp
Next
End Sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[VBAjedi]

Yah, yah, Skip - good call on the bottom up bit. But my way of seeing if the row is empty is cooler!



How about:

With ActiveSheet.UsedRange
  lFirstRow = .Row
  lLastRow = lFirstRow + .Rows.Count - 1
End With
For lRow = lLastRow To lFirstRow Step -1
  bEmpty = ActiveSheet.Rows(lRow).Find("*") Is Nothing
  If bEmpty Then Cells(lRow, iCol).EntireRow.Delete shift:=xlUp
Next

VBAjedi

 

[SkipVought]

I'll grant you that...

AND

===> STAR!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[VBAjedi]

Oops, left a reference to a deleted bit of code. Try:

With ActiveSheet.UsedRange
  lFirstRow = .Row
  lLastRow = lFirstRow + .Rows.Count - 1
End With
For lRow = lLastRow To lFirstRow Step -1
  bEmpty = ActiveSheet.Rows(lRow).Find("*") Is Nothing
  If bEmpty Then Cells

(lRow, 1)

.EntireRow.Delete shift:=xlUp
Next

(Ok, it was WAY too hard getting that to show up in red - I'm not gonna bother with that again. . . hope you all enjoyed it!)

VBAjedi

 

[Suggie]

Thanks, because I am new to VBA could you tell me how I could have the code just delete rows where columns C,D,E,F,G,H,J,K,Lare blank (columns A and B will always have data) I just need it to search from row 6 to row 200

Thanks again, stars on there way...

 

[Loomah]

Hi
As I'm the sort of no life that's hanging around TT on a Saturday......

Using yet another method of identifying empty rows you could try this. It will only test for data in columns C to L and only for rows 6 to 200

Sub a()
Dim lRow As Long
For lRow = 200 To 6 Step -1
    If [b]WorksheetFunction.CountA(Range(Cells(lRow, 3), Cells(lRow, 12))) = 0[/b] Then
        Rows(lRow).EntireRow.Delete
    End If
Next
End Sub

I notice that in your post you have missed out columns I in the list of columns you wish t test. I have assumed this to be a typo in the above routine but if it isn't a typo then this wil do the trick and ignore column I.

Sub b()
Dim lRow As Long
For lRow = 200 To 6 Step -1
    If [b]WorksheetFunction.CountA(Range(Cells(lRow, 3), Cells(lRow, 8)), _
        Range(Cells(lRow, 10), Cells(lRow, 12))) = 0[/b] Then
        Rows(lRow).EntireRow.Delete
    End If
Next
End Sub

World Champions!
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[Suggie]

Sorry to be lost on this but when I entered the above code I got an error : expected Expression Then or GOTo

Well done on the World Cup!

 

[VBAjedi]

Loomah was just trying to emphasize a part of his code by making it bold in his post, but the

[b] and [/b]

formatting tags didn't take (Loomah, it's probably cause they were inside code tags - I forget about that all the time!). Try his code without the tags:

Sub a()
Dim lRow As Long
For lRow = 20 To 6 Step -1
    If WorksheetFunction.CountA(Range(Cells(lRow, 3), Cells(lRow, 12))) = 0 Then
        Rows(lRow).EntireRow.Delete
    End If
Next
End Sub

Hope that does it for you!

VBAjedi

 

[Suggie]

Thanks for all your help, much appreciated.

 

[Suggie]

Sorry to drag this one up again but I am trying to combine two codes into one:

Basically : There is data in Columns A,B,C,D,E,F,G,H,I,J,K,L,M (From row 6 to 200)

Coulms A and B will always contain data

What I want the macro to do is that ONLY if ALL of the cells C through M are blank, then delete the ENTIRE row (including A+B), if not ignore.

Any ideas?

Thanks again,
Mark

 

[VBAjedi]

Mark,

That's exactly what that code snippet should do already(just adjust the last row# from 20 to 200 and the last column# from 12 to 13). What specifically is not working for you?

VBAjedi

 

[SBendBuckeye]

Hello,

Try this code and see if it helps you:

Sub TestIt()
Dim lngRow As Long
Dim lngBegRow As Long
Dim lngEndRow As Long
Dim strTemp As String
lngBegRow = 3 'Your starting row
lngEndRow = 21 'Your ending row
With Worksheets(&quot;Sheet1&quot 'Your actual sheet name
For lngRow = lngEndRow To lngBegRow Step -1
'Setup range to check (eg c5:m5)
strTemp = &quot;C&quot; & lngRow & &quot;:M&quot; & lngRow
MsgBox strTemp & &quot; &quot; & lngRow
If .Range(strTemp).Find(&quot;*&quot Is Nothing Then
.Rows(lngRow).EntireRow.Delete
End If
Next lngRow
End With
End Sub

Since you said you were new at VBA, here is what is happening in the code (if too simple no offence intended).

1. As noted above, you should ALWAYS start from the bottom and work up if you are deleting rows because everything shifts upwards when you delete a row. That's why I defined beginning and ending row values and used For Next Step -1

2. Using With SomeStatment is slightly more efficient while at the same time helping to make your code more readable. You take as much as is common and bracket it with a pair of With / End With statements. Here is an example:

No With / End With

Expr1.Expr2.Expr3.Expr4 = &quot;Yipee&quot;
Expr1.Expr2.Expr5.Expr3 = &quot;Yahoo&quot;

With / End With

With Expr1.Expr2 'Common piece up to period
.Expr3.Expr4 = &quot;Yipee&quot;
.Expr5.Expr3 = &quot;Yahoo&quot;
End With

Note that the editor is not forgiving with End With. If you forget and type EndIf it will split it to End If for you. If you forget and type EndWith it will not and flag it as an error when you try to run your code.

3. There are several ways to define ranges in code. One of the ways is the familiar way you refer to them in formulas (eg A1:B2). That is what I do above with strTemp.

4. The Find method works on a range of data. In the examples above they were checking the entire row, here I have changed it to check our defined range. If it finds anything it returns a collection (using the term loosely) of cells with data in them which is some kind of an object. An uninitialized object is said to be Nothing. So if none of the cells in the specifiec range contain any data, there is no object to return so it has a &quot;value&quot; of nothing.

5. If the range is completely empty we want to delete that row. You use Rows(YourRowNumber).EntireRow.Delete to accomplish that part of the task.

Don't be concerned if this all seems clear as mud. When you are new there is a lot to digest all at once. Please feel free to post again if you have further questions.

Good LucK!

Have a great day!

j2consulting@yahoo.com

 

[SBendBuckeye]

Hello Mark,

Somehow I missed VBAJedi's code just above my post. Here is another way to define a range in code using 2 pairs of cells:

Range(Cells(lRow, 3), Cells(lRow, 12))

If the row was 5, the above in formula style would be &quot;C5:M5&quot;. When you use Cells(x, y), x refers to the row number and y refers to the column number. So above, VBAJedi is refering to Col 3 (C) and Col 12 (L) (should be 13 (M)).

So the 2 ways would be Range(&quot;C5:M5&quot or
Range(Cells(5, 3), Cells(5, 13))

Good LucK!



Have a great day!

j2consulting@yahoo.com

 

[Suggie]

Thanks for the explanation and taking the time, really helps.

VBAJedi, thanks for the input, the problem is this code seems to be running slower than my original code (first post) it was firstly flagging cells in column A (Row 6 to 200) as &quot;blank row&quot; (using an if(or) formula in A6) then the next part of the code was deleting the rows flagged as blank row.

The problem I ran into with this code was when I started to use data validation in the columns C through M the if(or) formula did not flag them as &quot;blank rows&quot; even though all of the cells were blank in c through M and I cannot get if(isblank) to work correctly (tried array formula as well)

It's only slighty slower and I think this is because it is deleting individual rows (may be wrong). If anyone knows a faster way that would be great, ifnot, thanks for the above code.

Thanks again,

 

[Loomah]

Suggie
Which code are you using? The code I posted (suitable amended by VBAJedi) ignores columns A & B completely.

As for checking for blank cells individually using a formula see your thread thread68-713210. If you want to see if all cells in a range are blank you would need to use the AND operator not the OR operator.

It's hard to explain the difference without sounding patronising but basically OR will return TRUE in any of the cells are blank, AND will return true only if all the cells are blank.

I don't know that you can apply ISBLANK to a range - even as an array formula.

The probnlem with the validation is that the cells may not actually be blank or empty, ie if you have a space (&quot; &quot in cell A1 the =ISBLANK(A1) will return FALSE.

Enjoy
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[SBendBuckeye]

Loomah, thanks for the tag line. A nice chuckle to start the morning.

Have a great day!

j2consulting@yahoo.com

 

[Loomah]

>>I don't know that you can apply ISBLANK to a range - even as an array formula.

Well as it turns out you can:-
=AND(ISBLANK(A1:C3))
entered as an array formula (CTRL+SHIFT+ENTER) will only return true if ALL cells in the range are blank (like my mind at the mo'!)

Enjoy
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[Suggie]

Cheers, I like that.

I've tried entering the below as an array formula but it doensn't seem to work, although I'm on a review in Poland so I'm going to blame the PC (although it's probably me)

=IF(ISBLANK(C1:F1), &quot;Blank Row&quot;,&quot;Data Contained&quot