Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Fill format default | Excel Discussion (Misc queries) | |||
Default Fill Color | Excel Discussion (Misc queries) | |||
How do I set the default tab fill in excel? | Setting up and Configuration of Excel | |||
How to set the default color for fill in functon? | Setting up and Configuration of Excel | |||
Fill Default/Fill Copy | Excel Programming |