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. I’ve 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

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. I’ve 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
>

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" <(E-Mail Removed)> wrote in message
news:571730A3-5476-4B05-AAFD-(E-Mail Removed)...
>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. I’ve 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
>

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. I’ve 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
> >

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" <(E-Mail Removed)> wrote in message
> news:571730A3-5476-4B05-AAFD-(E-Mail Removed)...
> >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. I’ve 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
> >
>
>

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" <(E-Mail Removed)> wrote in message
newsBF680A0-08EA-46E1-822E-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:571730A3-5476-4B05-AAFD-(E-Mail Removed)...
>> >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. I’ve 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
>> >
>>
>>

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" <(E-Mail Removed)> wrote in message
> newsBF680A0-08EA-46E1-822E-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> >> news:571730A3-5476-4B05-AAFD-(E-Mail Removed)...
> >> >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. I’ve 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
> >> >
> >>
> >>
>
>