Gord Dibben said:This macro to fill blank cells in chosen Column will do the trick if I read this
right.
Sub Fill_Blanks()
'by Dave Peterson 2004-01-06
'fill blank cells in column with value above
Dim wks As Worksheet
Dim Rng As Range
Dim LastRow As Long
Dim col As Long
Set wks = ActiveSheet
With wks
col = ActiveCell.Column
'or
'col = .range("b1").column
Set Rng = .UsedRange 'try to reset the lastcell
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
Set Rng = Nothing
On Error Resume Next
Set Rng = .Range(.Cells(2, col), .Cells(LastRow, col)) _
.Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Rng Is Nothing Then
MsgBox "No blanks found"
Exit Sub
Else
Rng.FormulaR1C1 = "=R[-1]C"
End If
'replace formulas with values
With .Cells(1, col).EntireColumn
.Value = .Value
End With
End With
End Sub
You could also do it manually..........
Select Column A and F5>Special>Blanks>OK
Enter an = sign in active cell then point to cell above and hit CTRL + ENTER.
Copy Column A and Paste Special>Values>OK>Esc.
Gord Dibben MS Excel MVP
My spread sheet is over 33000 row long, I have an employee name in a1 and
need that same name copied into cells a2-a9 and then an other employee name
a13 and need it coped to cells a14-a20 and so forth. I'm trying to accomplish
so that I can use a filer to extract certain information but keep it attahced
to teh right employee.
Thanks
Thank you for your response.
I have tried this and I'm sure there is just one little thing that I do not
have right
but when I try to run teh macro I get and error message that says "invalid
outside procedure" and it highlights where it is "Set wks = ActiveSheet"
I have typed the macro as you sent it but I must not have it right.
Thanks again
Gord Dibben said:This macro to fill blank cells in chosen Column will do the trick if I read this
right.
Sub Fill_Blanks()
'by Dave Peterson 2004-01-06
'fill blank cells in column with value above
Dim wks As Worksheet
Dim Rng As Range
Dim LastRow As Long
Dim col As Long
Set wks = ActiveSheet
With wks
col = ActiveCell.Column
'or
'col = .range("b1").column
Set Rng = .UsedRange 'try to reset the lastcell
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
Set Rng = Nothing
On Error Resume Next
Set Rng = .Range(.Cells(2, col), .Cells(LastRow, col)) _
.Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Rng Is Nothing Then
MsgBox "No blanks found"
Exit Sub
Else
Rng.FormulaR1C1 = "=R[-1]C"
End If
'replace formulas with values
With .Cells(1, col).EntireColumn
.Value = .Value
End With
End With
End Sub
You could also do it manually..........
Select Column A and F5>Special>Blanks>OK
Enter an = sign in active cell then point to cell above and hit CTRL + ENTER.
Copy Column A and Paste Special>Values>OK>Esc.
Gord Dibben MS Excel MVP
My spread sheet is over 33000 row long, I have an employee name in a1 and
need that same name copied into cells a2-a9 and then an other employee name
a13 and need it coped to cells a14-a20 and so forth. I'm trying to accomplish
so that I can use a filer to extract certain information but keep it attahced
to teh right employee.
Thanks
:
I would like to write a macro to copy information from one cell to several
others and then copy other info from one cell to several other cells
Gord Dibben said:Do not type the code into a module.....copy it from the post and paste it in.
With workbook open hit ALT + F11 to get to Visual Basic Editor.
CTRL + r to open Project Explorer.
Select your workbook/project and right-click>Insert>Module.
Paste the code in there.
Add the line Option Explicit above the top line.
ALT + q to return to Excel window.
F8 to run the macro on the activecell column.
Gord
Thank you for your response.
I have tried this and I'm sure there is just one little thing that I do not
have right
but when I try to run teh macro I get and error message that says "invalid
outside procedure" and it highlights where it is "Set wks = ActiveSheet"
I have typed the macro as you sent it but I must not have it right.
Thanks again
Gord Dibben said:This macro to fill blank cells in chosen Column will do the trick if I read this
right.
Sub Fill_Blanks()
'by Dave Peterson 2004-01-06
'fill blank cells in column with value above
Dim wks As Worksheet
Dim Rng As Range
Dim LastRow As Long
Dim col As Long
Set wks = ActiveSheet
With wks
col = ActiveCell.Column
'or
'col = .range("b1").column
Set Rng = .UsedRange 'try to reset the lastcell
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
Set Rng = Nothing
On Error Resume Next
Set Rng = .Range(.Cells(2, col), .Cells(LastRow, col)) _
.Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Rng Is Nothing Then
MsgBox "No blanks found"
Exit Sub
Else
Rng.FormulaR1C1 = "=R[-1]C"
End If
'replace formulas with values
With .Cells(1, col).EntireColumn
.Value = .Value
End With
End With
End Sub
You could also do it manually..........
Select Column A and F5>Special>Blanks>OK
Enter an = sign in active cell then point to cell above and hit CTRL + ENTER.
Copy Column A and Paste Special>Values>OK>Esc.
Gord Dibben MS Excel MVP
My spread sheet is over 33000 row long, I have an employee name in a1 and
need that same name copied into cells a2-a9 and then an other employee name
a13 and need it coped to cells a14-a20 and so forth. I'm trying to accomplish
so that I can use a filer to extract certain information but keep it attahced
to teh right employee.
Thanks
:
I would like to write a macro to copy information from one cell to several
others and then copy other info from one cell to several other cells