Repeat a value down a column until a new value comes up

  • Thread starter Thread starter Randy
  • Start date Start date
R

Randy

I need assistance please. I want to repeat a value in the
blanks below it, but only until a new number comes next.
Then I want to repeat that value in the following blanks
until a new value shows up. Etc. If you can show me how to
automate that for a long list, I would appreciate knowing.
Here's an example.
5437
(blank)
(blank)
(blank)
0902
(blank)
(blank)
(blank)
2879
(blank)
(blank)
(blank)
 
Select the entire set of data, from the first value downwards. Do Edit / Go To
/ Special / Blanks, Type = and then hit the UP arrow once and then hit
CTRL+ENTER. Now copy the column and paste special as values.
 
Ken, I cannot make your idea work. I selected the column
of numbers (which are SSNs by the way). Then I clicked
Edit/Goto/Special followed by Blanks. Excel then gives me
a dialog box saying "No Cells were found". When I try to
hit "enter", Excel deletes the contents of the first cell.

Any idea what I can do to make your idea work?
 
Randy,

Works for me.....follow his directions closely.

-----Original Message-----
Ken, I cannot make your idea work. I selected the column
of numbers (which are SSNs by the way). Then I clicked
Edit/Goto/Special followed by Blanks. Excel then gives me
a dialog box saying "No Cells were found". When I try to
hit "enter", Excel deletes the contents of the first cell.

Any idea what I can do to make your idea work?
-----Original Message-----
Select the entire set of data, from the first value downwards. Do Edit / Go To
/ Special / Blanks, Type = and then hit the UP arrow once and then hit
CTRL+ENTER. Now copy the column and paste special as values.
Click to expand...
Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

--------------------------------------------------------
Click to expand...
-- -- how
to


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.725 / Virus Database: 480 - Release Date: 19/07/2004


.
Click to expand...
.
Click to expand...
 
Are you sure you have blanks then, ie assuming you have data in say A1 and then
'blanks' in A2:A5, if you in cell B2 put =ISBLANK(A2) and copy down to B5, do
you get TRUE for each cell?
 
You are right, Ken. The cells came back FALSE with
=ISBLANK, until I manually deleted the cell contents. Then
=ISBLANK came back TRUE.
How can I deal with that situation? (Note that the file is
a delimited text file brought into Excel.) I appreciate
your ideas.
 
Select all your data and then try running the following routine from David
McRitchie, and then see if they return TRUE. If so, you will then be able to
follow the original directions. It's a great routine to keep in your
PERSONAL.xls

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

Sub a_TrimALL()
'David McRitchie 2000-07-03 mod 2000-08-16 join.htm
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range
'Also Treat CHR 0160, as a space (CHR 032)
Selection.Replace what:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next 'in case no text cells in selection
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
cell.Value = Application.Trim(cell.Value)
Next cell
On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
 
Back
Top