Fill column with default value
 

Hello,

Hopefully this will be fairly simple. I have a spreadsheet containing
4 columns, the third column is blank. What I would like to do is fill
the blank column with a single character, specifically "P".

An example sheet would look like the following:

1 data data
2 data data
3 data data
4 data data

I would like an end result of:

1 data P data
2 data P data
3 data P data
4 data P data

Can someone help me?

Thanks,

-Nate

Fill column with default value
 

Assuming your sheet name is "sheet1" and your data starts from row 2.
Right click your sheet tab and paste below code.
Will do the job when you select the sheet.Or tell me
how and when you want the code runs.

Private Sub Worksheet_Activate()
Dim rng As Range
Dim i As Range

Application.EnableEvents = False
Application.ScreenUpdating = False

With Worksheets("sheet1")
Set rng = .Range("a2", .Range("a" & Rows.Count).End(xlUp))
End With

For Each i In rng
i.Offset(, 2).Value = "P"
Next i

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

Fill column with default value
 

I would like this to be the part of a macro that I've already
written. The macro I have performs a bunch of other operations
(column moves and row deletions, etc.) and I would like this to be the
last operation performed.

I tried using the following portions of your code with my macro:

Dim rng As Range
Dim i As Range
Set rng = .Range("a2", .Range("a" & Rows.Count).End(xlUp))
End With

For Each i In rng
i.Offset(, 2).Value = "P"
Next i

It gave me an error on the second '.Range' in the Set statement,
invalid identifier or something like that.

On Mar 15, 3:03 pm, wrote:
Assuming your sheet name is "sheet1" and your data starts from row 2.
Right click your sheet tab and paste below code.
Will do the job when you select the sheet.Or tell me
how and when you want the code runs.

Private Sub Worksheet_Activate()
Dim rng As Range
Dim i As Range

Application.EnableEvents = False
Application.ScreenUpdating = False

With Worksheets("sheet1")
Set rng = .Range("a2", .Range("a" & Rows.Count).End(xlUp))
End With

For Each i In rng
i.Offset(, 2).Value = "P"
Next i

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

Fill column with default value
 

Pls put back "With" bits :

With worksheets("yoursheet")
set rng............................................
end with

Dim rng As Range
Dim i As Range
Set rng = .Range("a2", .Range("a" & Rows.Count).End(xlUp))
End With

Fill column with default value
 

I put everything back and it ran. however, all I ended up with was a
"P" in C1 and C2 and all of the original data in the sheet was gone.

Any ideas?

On Mar 15, 4:03 pm, wrote:
Pls put back "With" bits :

With worksheets("yoursheet")
set rng............................................
end with

Dim rng As Range
Dim i As Range
Set rng = .Range("a2", .Range("a" & Rows.Count).End(xlUp))
End With

Fill column with default value
 

Hi Nate

If you are developing a macro always copy your data sheet first.
I don't know what your macro is doing.What the code I post does is
very simple : travel on column A and write a mere "P" on column C that
is all.
If you want to know what is wrong with your code pls post it.

rgds