Copying Cell Contents throughout spreadsheet

K

kaytee978

I'm new with programming in Visual Basic, and I am attempting to cop
cell contents into the cells below it.

For example, we have a spreadsheet lists each location in a cell, (ex
A1) and then has numerous assets listed throughout until it gets to th
next location (ex. A39). How can I get the first cell to copy into al
the empty cells until it gets to the next location
 
T

Tom Ogilvy

select the column

Do edit =>Goto Special and select Blanks

A2 should be the activeCell

in the formula bar type

=A1 then do Ctrl+Enter rather than Enter. This should fill all the blank
cells.

Now you can select column A and do Edit=>Copy, then immediately
Edit=>Pastespecial and select Values. This will replace the formulas with
the values they display.
 
K

kkknie

How about:

Code
-------------------
Sub test()

Dim i As Long
Dim iLastRow
Dim strLast As String

strLast = Range("A1").Value

'Assumes column B has data in the last row
iLastRow = Range("B65536").End(xlUp).Row

For i = 1 To iLastRow
'Checks for an empty cell
If Range("A" & i).Value = "" Then
Range("A" & i).Value = strLast
End If
strLast = Range("A" & i).Value
Next

End Su
-------------------

Assumptions:
- All of the A column cells are either blank or have data to be copie
down.
- Column B has data in the last row you want to copy to. If this i
not true, change from B65536 to C or D or wherever. If there is n
pattern, set iLastRow equal to the last row for data.
 
Top