Fill cells

[chrisnsmith]

Imagine a worksheet that looks something like this:

cell B6 = WH
B40=WK
B55=WN
B73=WU
B88=WZ

The rows between these have no entries in column B. Is there a way to fill
the cells between the WH and WK with WH, and cells between WK and WN with WK
and so on until all cells are filled.

 

[Sheeloo]

Yes, there is...

Select the range you want to fill in COL B starting at B6
Press F5, click on Special, click on BLANKS
Type =B6
and press CTRL-ENTER

 

[Rick Rothstein]

Is a VB solution acceptable? Also, you need to tell us what you mean by "and
so on until all cells are filled". Does that mean the WZ will be filled all
the way down to the bottom of the worksheet (Row 65536)? If not, how do we
know when to stop copying down?

 

[Shane Devenshire]

Hi,

And after you use the method suggest by Sheeloo, select all the cells in the
column and choose Copy, and then Edit, Paste Special, Values. If you don't a
sort command on column B will garbage your data.

 

[chrisnsmith]

A Vb solution is what I'm looking for. What I mean by, and so on, is to
repeat the procedure any time the procedure (macro) finds another cell in
column B with a different initial until it finds the last initial and fills
until it reaches a blank cell. The cells between initials all contain F1.
Sorry I wasn't more explicit. I hope this explains my needs.

 

[Rick Rothstein]

For future reference, you might consider posting to the
microsoft.public.excel.programming newsgroup when seeking VB solutions.

Okay, I'm still not 100% sure when at what row you want the fill process to
end, so in the code below, I am taking the dodge of using the Selection;
that is, the code works from the first cell in the selection to the last
cell in the selection... so select the range you want work over (selecting
the first cell with initials as the "top" cell in the Selection) and then
run this code...

Sub FillBlanksFromAbove()
Dim X As Long
With Selection
If .Columns.Count = 1 Then
For X = 2 To .Rows.Count
If .Item(X).Value = "" Then .Item(X).Value = .Item(X - 1).Value
Next
End If
End With
End Sub

The algorithm is simple enough that you should be able to change the code to
meet some exact range that you may want to operate over. The idea behind the
algorithm is to start in the 2nd cell down and see if it is blank... if so,
copy the cell's value above it, otherwise do nothing. This way, filled cells
will be skipped and blank cells will be filled in with the contents of the
cell above.

 

[Chip Pearson]

You can use code like

Sub AAA()
Dim FirstLine As Long
Dim N As Long
Dim LastLine As Long
Dim DataColumn As String
Dim WS As Worksheet

FirstLine = 1 '<<< CHANGE AS REQUIRED
DataColumn = "B" '<<< CHANGE AS REQUIRED
Set WS = Worksheets("Sheet1") '<<< CHANGE AS REQUIRED
With WS
LastLine = .Cells(.Rows.Count, DataColumn).End(xlUp).Row
For N = FirstLine To LastLine
If .Cells(N, DataColumn).Value = vbNullString Then
.Cells(N, DataColumn).Value = _
.Cells(N - 1, DataColumn)
End If
Next N
End With

End Sub


Change the lines marked with <<<< to the appropriate values.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[chrisnsmith]

That worked great Chip, but now I'm sorry to say I need it to work on 4
worksheets. Can you tell me what I need to change?

 

[Chip Pearson]

Try the following code. It works in two ways. You can select any
number of worksheets (hold down the CTRL key and click the worksheet
tabs) and the code will do the fill operation on the selected sheets.
Or, you can code the worksheet names directly in the code.

Sub AAA()
Dim FirstLine As Long
Dim N As Long
Dim LastLine As Long
Dim DataColumn As String
Dim WS As Variant
Dim WSS() As Worksheet

With ActiveWindow.SelectedSheets
If .Count > 1 Then
ReDim WSS(1 To .Count)
For N = 1 To .Count
Set WSS(N) = .Item(N)
Next N
Else
ReDim WSS(1 To 4)
Set WSS(1) = Worksheets("Sheet1") '<<< CHANGE AS REQUIRED
Set WSS(2) = Worksheets("Sheet2") '<<< CHANGE AS REQUIRED
Set WSS(3) = Worksheets("Sheet3") '<<< CHANGE AS REQUIRED
Set WSS(4) = Worksheets("Sheet4") '<<< CHANGE AS REQUIRED
End If
For Each WS In WSS
FirstLine = 1 '<<< CHANGE AS REQUIRED
DataColumn = "B" '<<< CHANGE AS REQUIRED
With WS
LastLine = .Cells(.Rows.Count, DataColumn).End(xlUp).Row
For N = FirstLine To LastLine
If .Cells(N, DataColumn).Value = vbNullString Then
.Cells(N, DataColumn).Value = _
.Cells(N - 1, DataColumn)
End If
Next N
End With
Next WS
End With

End Sub

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[chrisnsmith]

Worked great Chip. Thank you.

Try the following code. It works in two ways. You can select any
number of worksheets (hold down the CTRL key and click the worksheet
tabs) and the code will do the fill operation on the selected sheets.
Or, you can code the worksheet names directly in the code.

Sub AAA()
Dim FirstLine As Long
Dim N As Long
Dim LastLine As Long
Dim DataColumn As String
Dim WS As Variant
Dim WSS() As Worksheet

With ActiveWindow.SelectedSheets
If .Count > 1 Then
ReDim WSS(1 To .Count)
For N = 1 To .Count
Set WSS(N) = .Item(N)
Next N
Else
ReDim WSS(1 To 4)
Set WSS(1) = Worksheets("Sheet1") '<<< CHANGE AS REQUIRED
Set WSS(2) = Worksheets("Sheet2") '<<< CHANGE AS REQUIRED
Set WSS(3) = Worksheets("Sheet3") '<<< CHANGE AS REQUIRED
Set WSS(4) = Worksheets("Sheet4") '<<< CHANGE AS REQUIRED
End If
For Each WS In WSS
FirstLine = 1 '<<< CHANGE AS REQUIRED
DataColumn = "B" '<<< CHANGE AS REQUIRED
With WS
LastLine = .Cells(.Rows.Count, DataColumn).End(xlUp).Row
For N = FirstLine To LastLine
If .Cells(N, DataColumn).Value = vbNullString Then
.Cells(N, DataColumn).Value = _
.Cells(N - 1, DataColumn)
End If
Next N
End With
Next WS
End With

End Sub

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)