Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
moving data dynamically based on empty cell
I have a data sheet that looks like this...
col a col b abc 10 20 45 efg 5 18 10 I need it to look like this... col a col b abc 10 abc 20 abc 45 efg 5 efg 18 efg 10 since there is no consistency to the number of rows in col b with data, i have to come up with a formula that is dynamic The file has 20,000 rows so doing it manually is not the way to go. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
moving data dynamically based on empty cell
Give this macro a try...
Sub FillInTheBlanks() Dim Area As Range, LastRow As Long Const ColLetter As String = "A" On Error Resume Next LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, _ SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row For Each Area In Columns(ColLetter)(1).Resize(LastRow). _ SpecialCells(xlCellTypeBlanks).Areas Area.Value = Area(1).Offset(-1).Value Next End Sub -- Rick (MVP - Excel) "stickandrock" wrote in message ... I have a data sheet that looks like this... col a col b abc 10 20 45 efg 5 18 10 I need it to look like this... col a col b abc 10 abc 20 abc 45 efg 5 efg 18 efg 10 since there is no consistency to the number of rows in col b with data, i have to come up with a formula that is dynamic The file has 20,000 rows so doing it manually is not the way to go. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamically reference a cell based on cell content | Excel Discussion (Misc queries) | |||
Error MOVING data: Cannot empty the clipboard | Excel Discussion (Misc queries) | |||
Dynamically setting CELL COLORS based on TWO OTHER cell values | Excel Discussion (Misc queries) | |||
Moving data if cell is empty | Excel Programming | |||
How to add a Combobox dynamically based on contents of some other cell? | Excel Programming |