macro row delete help

[plost33]

hi all. i have a spreadsheet sheet that has possible values in the range H7:N21

I want to delete an entire row if that row does not have a value in the range between H:N

i beleive this is a loop inside a loop but i think i am way over complicating things. here is my loops i have now:

 For i = 7 To 21
    With Range("H" & i)
        If .Value = "" Then
            For c = 9 To 14
                With Range(c & i)
                    If .Value = "" Then
                        Next c
                    Else
                        Next i
                    End If
                End With
       Else
        Next i
   End With

hope someone can assist.

 

[VoG]

Try

For i = 21 To 7 Step - 1
    With Range("H" & i)
        If WorksheetFunction.CountA(.Resize(, 7).Value) = 0 Then Rows(i).Delete
    End With
Next i

 

[plost33]

Vog, that is not deleting any rows.

I cycles through but does not delete any rows.

 

[plost33]

anyone else see a way to get this working?

 

[Shawnyv]

Plost, your original macro doesn't seem to have a row delete command anywhere in it - you just tell it whether to move to the next row or next column, but you don't tell it what to do in the case that it gets to the end of a row having found no values.

 

[plost33]

yes i know that. i need to add that in...as it is currently i am getting an error saying i dont have a "next" for the "c" ...so i did not add that in yet. i think i am making it a bit to difficult...

 

[Shawnyv]

Here's a really bad version of the macro that will work:

Range("H7").Select
del = 0
For r = 7 To 21
For c = 1 To 8
If ActiveCell.Value <> "" Then
del = 1
End If
ActiveCell.Offset(0, 1).Select
Next c
If del = 0 Then
ActiveCell.EntireRow.Delete
ActiveCell.Offset(0, -8).Select
Else
ActiveCell.Offset(1, -8).Select
End If
del = 0
Next r

 

[plost33]

does anyone know a better way to do this?

I want to delete the entire row if none of the cells on that row have value in between column H and N.

this should apply to rows 7 through 21



hope someone can help.

 

[Shawnyv]

does anyone know a better way to do this?

I want to delete the entire row if none of the cells on that row have value in between column H and N.

this should apply to rows 7 through 21



hope someone can help.

The macro I posted will do just what you ask - it's inelegant, but it works.

 

[plost33]

shawny. thanks that works great.