Only show rows with empty cells inbetween

[Baloubeer]

Hi All,

I have a list in excel with a fixed amount of rows (8 till about 1000) and each row has a variable amount of columns.
Within each row sometimes, the value is empty.
I need to write a VBA script to only show me the rows that contain cells with empty values between column C and the last column of each row with a value.
In below picture for example you can see these lines in red.
Line 3 has D3 empty
Line 6 has E6 empty
line 10 had G10 empty
line 13 has H13 empty
The green lines can be hided

I managed to find last row with values and I'm able to find last column in a row with a value but somehow I can't tie it together.

All help is appreciated.

Thanks in advance.
Wim

 

[JoeMo]

This will check all rows between the first column in the used range and the last column in the row for empty cell(s), and hide the row if there are none.

Sub Baloubeer()
Dim R As Range, FirstCol As Long, Rw As Range, Lcol As Long, HideRws As Range
Set R = ActiveSheet.UsedRange
For Each Rw In R.Rows
    Lcol = Rw.Find(what:="*", searchdirection:=xlPrevious).Column
    FirstCol = Rw.Cells(1, 1).Column
    If Application.CountA(Range(Cells(Rw.Row, FirstCol), Cells(Rw.Row, Lcol))) = Range(Cells(Rw.Row, FirstCol), Cells(Rw.Row, Lcol)).Count Then
        If HideRws Is Nothing Then
           Set HideRws = Rw.EntireRow
        Else
            Set HideRws = Union(HideRws, Rw.EntireRow)
        End If
    End If
Next Rw
If Not HideRws Is Nothing Then
    HideRws.EntireRow.Hidden = True
Else
    MsgBox "No rows without at least one empty cell found"
End If
End Sub

 

[Peter_SSs]

I'm not sure if ..

• You physically want to hide rows like @JoeMo's code does or simply highlight the relevant rows with colour, or
• It is possible to have rows like 5 and/or 6 below

If just highlighting, could you consider Conditional Formatting instead of vba?

Baloubeer.xlsm
	C	D	E	F	G	H	I	J
1	data	data	data
2	data	data		data	data	data	data	data
3	data	data	data	data	data	data	data	data
4	data			data		data
5
6					data	data	data	data
7	data							data
8	data		data		data		data	data
Sheet4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1:J8	Expression	=XMATCH("?*",$C1:$J1,2,-1)-XMATCH("?*",$C1:$J1,2)+1<>COUNTIF($C1:$J1,"?*")	text	NO