excel vba to remove all rows that have only a value in column E and ignore blanks

[BORUCH]

hi

i have the below sheet

Book1
	A	B	C	D	E	F	G	H	I	J	K
11	ITEM	DESCRIPTION	misc info 1	misc info 2	misc info 3	misc info 4	misc info 5	misc info 6	misc info 7	misc info 8	misc info 9
12	00000	MISC 1			$1.00	1	$1.00	1	$1.00	1	$1.00
13	00001	MISC 2
14	00003	MISC 3
15
16	00004	MISC 5			$1.00	3	$55.00	1	$1.00	1	$1.00
17	00005	MISC 6
18
19	00006	MISC 8			$45.00
20	00007	MISC 9
21
22	00008	MISC 11			$1.00	1	$1.00	1	$1.00	1	$1.00
23	00009	MISC 12
24
25	00010	MISC 14			$1.00	1	$1.00	1	$1.00	1	$1.00
26	00011	MISC 15
27	00012	MISC 16
28	ab123	MISC 17
29
30	ASD111	MISC 19			$67.00
31	222222	MISC 20
32	33333	MISC 21
33	444	MISC 22
34	55555	MISC 23
Sheet1

i would like to remove all rows that only have a value in column E and ignore all empty rows

thanks

 

[davesexcel]

Your cells are merged, what results would be expected?

 

[BORUCH]

Your cells are merged, what results would be expected?

rows 19,20 30,31,32,33,34 to be removed

 

[JEC]

Never use merged cells, especially when using VBA.
This might work for you

Sub jec()
 Dim it
 For Each it In Range("A2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(2).Offset(, 4).Areas
   If Len(it.Offset) And IsEmpty(it.Offset(, 1)) Then it.EntireRow.Delete
 Next
End Sub

 

[BORUCH]

Never use merged cells, especially when using VBA.
This might work for you

Sub jec()
 Dim it
 For Each it In Range("A2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(2).Offset(, 4).Areas
   If Len(it.Offset) And IsEmpty(it.Offset(, 1)) Then it.EntireRow.Delete
 Next
End Sub

im getting an error type mismatch

 

[JEC]

and now?

Sub jec()
 Dim r, i As Long
 Set r = Range("A2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(2).Offset(, 4).Areas
 
 For i = r.Count To 1 Step -1
   If Len(r(i).Offset) And IsEmpty(r(i).Offset(, 1)) Then r(i).EntireRow.Delete
 Next
End Sub

 

[BORUCH]

and now?

Sub jec()
 Dim r, i As Long
 Set r = Range("A2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(2).Offset(, 4).Areas
 
 For i = r.Count To 1 Step -1
   If Len(r(i).Offset) And IsEmpty(r(i).Offset(, 1)) Then r(i).EntireRow.Delete
 Next
End Sub

same error on this code

If Len(r(i).Offset) And IsEmpty(r(i).Offset(, 1)) Then

 

[JEC]

Never use merged cells, especially when using VBA.

 

[BORUCH]

is there a workaround ? i need to have it merged

 

[davesexcel]

rows 19,20 30,31,32,33,34 to be removed

why not 12-14 or 16-17?