Copy Down
 

I would like to create a macro that will search every cell in column K. When
a blank cell is found in Column K, I want the macro to copy the contents in
the cell above it and paste those contents in each blank cell in Column K
going down until the next cell with contents is reached. Ive been working
with this code but unsuccessfully. It was originally intended for a range of
columns.

Sub FillBlankRows()
Dim BlankCell As Integer
Dim r As Long
Dim col As Long

For r = 3 To 100
For col = 11 to 11
If Cells(r, col).Value = "" Then
BlankCell = BlankCell + 1
End If
Next
If BlankCell = 11 Then
Range("K" & r - 1 & ":K" & r - 1).Copy Range("K" & r)

End If
BlankCell = 0
Next
End Sub

Copy Down
 

Sub WaitABit()
Dim cel As Range
For Each cel In Range("K:K")
If cel.Value = "" Then
cel.Value = cel.Offset(-1, 0).Value
End If
Next cel
End Sub



K1 must not be empty
--
Gary''s Student - gsnu200844


"MCheru" wrote:

I would like to create a macro that will search every cell in column K. When
a blank cell is found in Column K, I want the macro to copy the contents in
the cell above it and paste those contents in each blank cell in Column K
going down until the next cell with contents is reached. Ive been working
with this code but unsuccessfully. It was originally intended for a range of
columns.

Sub FillBlankRows()
Dim BlankCell As Integer
Dim r As Long
Dim col As Long

For r = 3 To 100
For col = 11 to 11
If Cells(r, col).Value = "" Then
BlankCell = BlankCell + 1
End If
Next
If BlankCell = 11 Then
Range("K" & r - 1 & ":K" & r - 1).Copy Range("K" & r)

End If
BlankCell = 0
Next
End Sub

Copy Down
 

Give this a try...

Sub FillBlanksInColumnK()
Dim R As Range
Dim Blanks As Range
Dim LastRow As Long
On Error GoTo Whoops
With Worksheets("Sheet4")
LastRow = .Cells(.Rows.Count, "K").End(xlUp).Row
Set Blanks = .Range("K2:K" & LastRow).SpecialCells(xlCellTypeBlanks)
For Each R In Blanks
R.Value = R.Offset(-1).Value
Next
End With
Exit Sub
Whoops:
MsgBox "There are no blank cells!"
End Sub

--
Rick (MVP - Excel)


"MCheru" wrote in message
...
I would like to create a macro that will search every cell in column K.
When
a blank cell is found in Column K, I want the macro to copy the contents
in
the cell above it and paste those contents in each blank cell in Column K
going down until the next cell with contents is reached. Ive been
working
with this code but unsuccessfully. It was originally intended for a range
of
columns.

Sub FillBlankRows()
Dim BlankCell As Integer
Dim r As Long
Dim col As Long

For r = 3 To 100
For col = 11 to 11
If Cells(r, col).Value = "" Then
BlankCell = BlankCell + 1
End If
Next
If BlankCell = 11 Then
Range("K" & r - 1 & ":K" & r - 1).Copy Range("K" & r)

End If
BlankCell = 0
Next
End Sub

Copy Down
 

Thank you. This works amazing! Could this be modified so that if no
contents are in column J it stops?

"Gary''s Student" wrote:

Sub WaitABit()
Dim cel As Range
For Each cel In Range("K:K")
If cel.Value = "" Then
cel.Value = cel.Offset(-1, 0).Value
End If
Next cel
End Sub



K1 must not be empty
--
Gary''s Student - gsnu200844


"MCheru" wrote:

I would like to create a macro that will search every cell in column K. When
a blank cell is found in Column K, I want the macro to copy the contents in
the cell above it and paste those contents in each blank cell in Column K
going down until the next cell with contents is reached. Ive been working
with this code but unsuccessfully. It was originally intended for a range of
columns.

Sub FillBlankRows()
Dim BlankCell As Integer
Dim r As Long
Dim col As Long

For r = 3 To 100
For col = 11 to 11
If Cells(r, col).Value = "" Then
BlankCell = BlankCell + 1
End If
Next
If BlankCell = 11 Then
Range("K" & r - 1 & ":K" & r - 1).Copy Range("K" & r)

End If
BlankCell = 0
Next
End Sub

Copy Down
 

Thank you for your help. This is outstanding. Could this be modified so
that if no contents are in column J it stops?

"Rick Rothstein" wrote:

Give this a try...

Sub FillBlanksInColumnK()
Dim R As Range
Dim Blanks As Range
Dim LastRow As Long
On Error GoTo Whoops
With Worksheets("Sheet4")
LastRow = .Cells(.Rows.Count, "K").End(xlUp).Row
Set Blanks = .Range("K2:K" & LastRow).SpecialCells(xlCellTypeBlanks)
For Each R In Blanks
R.Value = R.Offset(-1).Value
Next
End With
Exit Sub
Whoops:
MsgBox "There are no blank cells!"
End Sub

--
Rick (MVP - Excel)


"MCheru" wrote in message
...
I would like to create a macro that will search every cell in column K.
When
a blank cell is found in Column K, I want the macro to copy the contents
in
the cell above it and paste those contents in each blank cell in Column K
going down until the next cell with contents is reached. Ive been
working
with this code but unsuccessfully. It was originally intended for a range
of
columns.

Sub FillBlankRows()
Dim BlankCell As Integer
Dim r As Long
Dim col As Long

For r = 3 To 100
For col = 11 to 11
If Cells(r, col).Value = "" Then
BlankCell = BlankCell + 1
End If
Next
If BlankCell = 11 Then
Range("K" & r - 1 & ":K" & r - 1).Copy Range("K" & r)

End If
BlankCell = 0
Next
End Sub

Copy Down
 

Does this do what you want?

Sub FillBlanksInColumnK()
Dim R As Range
Dim Blanks As Range
Dim LastRow As Long
On Error GoTo Whoops
With Worksheets("Sheet4")
LastRow = .Cells(.Rows.Count, "J").End(xlUp).Row
Set Blanks = .Range("K2:K" & LastRow).SpecialCells(xlCellTypeBlanks)
For Each R In Blanks
R.Value = R.Offset(-1).Value
Next
End With
Exit Sub
Whoops:
MsgBox "There are no blank cells!"
End Sub

--
Rick (MVP - Excel)


"MCheru" wrote in message
...
Thank you for your help. This is outstanding. Could this be modified so
that if no contents are in column J it stops?

"Rick Rothstein" wrote:

Give this a try...

Sub FillBlanksInColumnK()
Dim R As Range
Dim Blanks As Range
Dim LastRow As Long
On Error GoTo Whoops
With Worksheets("Sheet4")
LastRow = .Cells(.Rows.Count, "K").End(xlUp).Row
Set Blanks = .Range("K2:K" & LastRow).SpecialCells(xlCellTypeBlanks)
For Each R In Blanks
R.Value = R.Offset(-1).Value
Next
End With
Exit Sub
Whoops:
MsgBox "There are no blank cells!"
End Sub

--
Rick (MVP - Excel)


"MCheru" wrote in message
...
I would like to create a macro that will search every cell in column K.
When
a blank cell is found in Column K, I want the macro to copy the
contents
in
the cell above it and paste those contents in each blank cell in Column
K
going down until the next cell with contents is reached. Ive been
working
with this code but unsuccessfully. It was originally intended for a
range
of
columns.

Sub FillBlankRows()
Dim BlankCell As Integer
Dim r As Long
Dim col As Long

For r = 3 To 100
For col = 11 to 11
If Cells(r, col).Value = "" Then
BlankCell = BlankCell + 1
End If
Next
If BlankCell = 11 Then
Range("K" & r - 1 & ":K" & r - 1).Copy Range("K" & r)

End If
BlankCell = 0
Next
End Sub

Copy Down
 

Wow, this is exactly what I was looking for. Thank you so very very much.

"Rick Rothstein" wrote:

Does this do what you want?

Sub FillBlanksInColumnK()
Dim R As Range
Dim Blanks As Range
Dim LastRow As Long
On Error GoTo Whoops
With Worksheets("Sheet4")
LastRow = .Cells(.Rows.Count, "J").End(xlUp).Row
Set Blanks = .Range("K2:K" & LastRow).SpecialCells(xlCellTypeBlanks)
For Each R In Blanks
R.Value = R.Offset(-1).Value
Next
End With
Exit Sub
Whoops:
MsgBox "There are no blank cells!"
End Sub

--
Rick (MVP - Excel)


"MCheru" wrote in message
...
Thank you for your help. This is outstanding. Could this be modified so
that if no contents are in column J it stops?

"Rick Rothstein" wrote:

Give this a try...

Sub FillBlanksInColumnK()
Dim R As Range
Dim Blanks As Range
Dim LastRow As Long
On Error GoTo Whoops
With Worksheets("Sheet4")
LastRow = .Cells(.Rows.Count, "K").End(xlUp).Row
Set Blanks = .Range("K2:K" & LastRow).SpecialCells(xlCellTypeBlanks)
For Each R In Blanks
R.Value = R.Offset(-1).Value
Next
End With
Exit Sub
Whoops:
MsgBox "There are no blank cells!"
End Sub

--
Rick (MVP - Excel)


"MCheru" wrote in message
...
I would like to create a macro that will search every cell in column K.
When
a blank cell is found in Column K, I want the macro to copy the
contents
in
the cell above it and paste those contents in each blank cell in Column
K
going down until the next cell with contents is reached. Ive been
working
with this code but unsuccessfully. It was originally intended for a
range
of
columns.

Sub FillBlankRows()
Dim BlankCell As Integer
Dim r As Long
Dim col As Long

For r = 3 To 100
For col = 11 to 11
If Cells(r, col).Value = "" Then
BlankCell = BlankCell + 1
End If
Next
If BlankCell = 11 Then
Range("K" & r - 1 & ":K" & r - 1).Copy Range("K" & r)

End If
BlankCell = 0
Next
End Sub