VBA Help Please (delete row)

[Scott Wagner]

In a previous post Don Guillett helped me and provided the following code. I
need to change this macro, but don't have a clue how. Hoping you can help.

Sub fillinblanksN()
lr = Cells(Rows.Count, "c").End(xlUp).Row
For Each c In Range("d2:d" & lr)
If Len(c) < 2 Then
c.Offset(0, -3) = c.Offset(-1, -3)
c.Offset(0, -2) = c.Offset(-1, -2)
End If
Next
End Sub

There are lines in my sheet now that have a designation (ColA) that I need
to delete when this macro runs. In the example below I'm focusing on the two
"panelboard" lines. Take a look and let me know if you know how to solve
this issue. ??? .EntireRow.Delete ???

Below is an example

What I have now:
ColA ColB ColC ColD
1 Starter Open Type
3 XFMR Dry Type
AAA 4 Panelboard Type B
1 Box
1 Trim
BBB 2 Panelboard Type Q
1 Box
1 Trim
1 Ground Bar

What it looks like after macro above runs:
ColA ColB ColC ColD
1 Starter Open Type
3 XFMR Dry Type
AAA 4 Panelboard Type B
AAA 4 Box
AAA 4 Trim
BBB 2 Panelboard Type Q
BBB 2 Box
BBB 2 Trim
BBB 2 Ground Bar

What I need to end up with:
ColA ColB ColC ColD
1 Starter Open Type
3 XFMR Dry Type
AAA 4 Box
AAA 4 Trim
BBB 2 Box
BBB 2 Trim
BBB 2 Ground Bar

 

[Tom Ogilvy]

Sub fillinblanksN()
lr = Cells(Rows.Count, "c").End(xlUp).Row
For Each c In Range("d2:d" & lr)
If Len(c) < 2 Then
c.Offset(0, -3) = c.Offset(-1, -3)
c.Offset(0, -2) = c.Offset(-1, -2)
End If
Next
for i = lr - 1 to 2 step -1
if cells(i,1) <> cells(i-1,1) and _
not isempty(cells(i-1,1) ) and _
cells(i,1) = cells(i+1,1)then
rows(i).Delete
end if
Next
End sub

 

[Scott Wagner]

Tom,

It skiped the first line I was hoping to delete (panelboard AAA), and
deleted one I was hoping not to delete (starter). Any ideas?

This is what it produced:
ColA ColB ColC ColD
3 XFMR Dry Type
AAA 4 Panelboard Type B
AAA 4 Box
AAA 4 Trim
BBB 2 Box
BBB 2 Trim
BBB 2 Ground Bar

 

[Tom Ogilvy]

I can't reproduce the "starter" row being deleted. is the word starter in
C3


In any event, try this:

Sub fillinblanksN()
lr = Cells(Rows.Count, "c").End(xlUp).Row
For Each c In Range("d2:d" & lr)
If Len(c) < 2 Then
c.Offset(0, -3) = c.Offset(-1, -3)
c.Offset(0, -2) = c.Offset(-1, -2)
End If
Next
for i = lr - 1 to 3 step -1
if cells(i,1) <> cells(i-1,1) and _
cells(i,1) = cells(i+1,1)then
rows(i).Delete
end if
Next
End sub

change the 3 in for i = lr - 1 to 3 step -1

to reflect the first row you want checked.

--
Regards,
Tom Ogilvy

 

[Scott Wagner]

That worked. Starter was in cell C2.

Thank you sir!