Set up an If Macro

[Melissa]

Trying to write a macro to fill in the blank spots in column B. So if B2 =
"" Then "Not ATT" if cell is not blank then skip cell, retain data and move
to the next and keep processing until all data reviewed.

A B
1 John Smith ATT
2 Tom Jones
3 Jane Henry DNS

 

[Don Guillett]

How about selecting the range>edit>replace>leave blank>with Not ATT

 

[Wilson]

Hi

Sub Fillblanks()
Dim lr As Long
Rows("1:1").Insert Shift:=xlDown
lr = Cells(Rows.Count, "A").End(xlUp).Row
Range("A1:B1").AutoFilter
Selection.AutoFilter Field:=2, Criteria1:="="
Range("B2:B" & lr) = "Not ATT"
Rows("1:1").Delete
End Sub

Copy code above
Alt+F11 to invoke VB Editor
Insert>Module
Paste code into white pane that appears
Alt+F11 to rturn to Excel

To use
Alt+F8
Select macro Fillblanks>Run

Roger -

How would I need to change the macro if I wanted to get the cell populated
with the data from the last field that it found was populated... for example:

I have the following data:

A | B

10 |
| Adam
| Mike
| John

12 |
| Stan
| Frank

and I want:

10 |
10 | Adam
10 | Mike
10 | John
12 |
12 | Stan
12 | Frank

I could obviously just drag down, but there are just too many.

THanks,

 

[Roger Govier]

Hi

the following should do what you want

Sub Fillblanks2()
Application.ScreenUpdating = False
Dim lr As Long, i As Long

lr = Cells(Rows.Count, "B").End(xlUp).Row
For i = 2 To lr
If Cells(i, 1) = "" And Cells(i, 2) = "" Then Rows(i).Delete
If Cells(i, 2) <> "" Then
Cells(i, 1) = Cells(i - 1, 1).Value
End If
Next i
Application.ScreenUpdating = True
End Sub

I have followed your example literally, and if there is a blank row between
the sets of data, then it gets deleted.
If you don't want that, then just remove the line
If Cells(i, 1) = "" And Cells(i, 2) = "" Then Rows(i).Delete