| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy Up until Non-Blank Cell encountered
I need a procedure that will start with say cell L300 (which has a text
value) and Copy it into cells L299-L250 that are blank; L249 has a different Cell value and I need to copy it into cells L248-L150 that are blank, etc until Row 2. How would I do that? Do while.. Loop but I can't solve... Any help appreciated |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Copy Up until Non-Blank Cell encountered
Sub fillinblanks()
mycol = "L" For i = Cells(Rows.Count, mycol).End(xlUp).Row To 2 Step -1 If Cells(i - 1, mycol) = "" Then Cells(i - 1, mycol).Value = Cells(i, mycol) End If Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Jim May" wrote in message ... I need a procedure that will start with say cell L300 (which has a text value) and Copy it into cells L299-L250 that are blank; L249 has a different Cell value and I need to copy it into cells L248-L150 that are blank, etc until Row 2. How would I do that? Do while.. Loop but I can't solve... Any help appreciated |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Copy Up until Non-Blank Cell encountered
Assuming the cells are truely blank (not formulas returning blank or such)
then this should do it... Sub CopyLotsOfStuff() Call CopyStuff(Range("L300")) Call CopyStuff(Range("L249")) End Sub Sub CopyStuff(ByVal rngToCopy As Range) Dim rngToPaste As Range Set rngToPaste = rngToCopy.Offset(-50, _ 0).Resize(49).SpecialCells(xlCellTypeBlanks) rngToPaste.Value = rngToCopy.Value End Sub -- HTH... Jim Thomlinson "Jim May" wrote: I need a procedure that will start with say cell L300 (which has a text value) and Copy it into cells L299-L250 that are blank; L249 has a different Cell value and I need to copy it into cells L248-L150 that are blank, etc until Row 2. How would I do that? Do while.. Loop but I can't solve... Any help appreciated |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Copy Up until Non-Blank Cell encountered
I Have:
within Sub FindlRow: .... Lrow = Cells(i, 1).Row Exit For End If Next i End With ActiveSheet.Range("A2:G" & Lrow).Copy Sheets("Main").Range("A2").PasteSpecial Paste:=xlValues CutCopyMode = False With Sheets("Main") .Activate .Calculate .Range("I2").Select End With With Sheets("Filter") .Range("A2:M5000").ClearContents End With ActiveSheet.Range("I2:T" & Lrow).Copy Sheets("Filter").Range("A2").PasteSpecial Paste:=xlValues CutCopyMode = False Sheets("Filter").Activate Range("b2").Select Call CopyGrpUp <<< See below End Sub Sub CopyGrpUp() ' Your code modified... mycol = "T" For j = Cells(Lrow, mycol).End(xlUp).Row To 2 Step -1 " R/t 1004 Occurs here !! If Cells(j - 1, mycol) = "" Then Cells(j - 1, mycol).Value = Cells(j, mycol) End If Next j End Sub But when I run I get R/T 1004 Lrow "Don Guillett" wrote: Sub fillinblanks() mycol = "L" For i = Cells(Rows.Count, mycol).End(xlUp).Row To 2 Step -1 If Cells(i - 1, mycol) = "" Then Cells(i - 1, mycol).Value = Cells(i, mycol) End If Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Jim May" wrote in message ... I need a procedure that will start with say cell L300 (which has a text value) and Copy it into cells L299-L250 that are blank; L249 has a different Cell value and I need to copy it into cells L248-L150 that are blank, etc until Row 2. How would I do that? Do while.. Loop but I can't solve... Any help appreciated |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Copy Up until Non-Blank Cell encountered
Never Mind Don,, I worked it out.. Thanks for the help
Jim "Jim May" wrote: I Have: within Sub FindlRow: ... Lrow = Cells(i, 1).Row Exit For End If Next i End With ActiveSheet.Range("A2:G" & Lrow).Copy Sheets("Main").Range("A2").PasteSpecial Paste:=xlValues CutCopyMode = False With Sheets("Main") .Activate .Calculate .Range("I2").Select End With With Sheets("Filter") .Range("A2:M5000").ClearContents End With ActiveSheet.Range("I2:T" & Lrow).Copy Sheets("Filter").Range("A2").PasteSpecial Paste:=xlValues CutCopyMode = False Sheets("Filter").Activate Range("b2").Select Call CopyGrpUp <<< See below End Sub Sub CopyGrpUp() ' Your code modified... mycol = "T" For j = Cells(Lrow, mycol).End(xlUp).Row To 2 Step -1 " R/t 1004 Occurs here !! If Cells(j - 1, mycol) = "" Then Cells(j - 1, mycol).Value = Cells(j, mycol) End If Next j End Sub But when I run I get R/T 1004 Lrow "Don Guillett" wrote: Sub fillinblanks() mycol = "L" For i = Cells(Rows.Count, mycol).End(xlUp).Row To 2 Step -1 If Cells(i - 1, mycol) = "" Then Cells(i - 1, mycol).Value = Cells(i, mycol) End If Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Jim May" wrote in message ... I need a procedure that will start with say cell L300 (which has a text value) and Copy it into cells L299-L250 that are blank; L249 has a different Cell value and I need to copy it into cells L248-L150 that are blank, etc until Row 2. How would I do that? Do while.. Loop but I can't solve... Any help appreciated |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Copy Up until Non-Blank Cell encountered
Some cleaning up perhaps. Could even be better using with for sheets("main")
Have: within Sub FindlRow: .... Lrow = Cells(i, 1).Row Exit For End If Next i End With 'dont understand above to get Lrow? ActiveSheet.Range("A2:G" & Lrow).Copy Sheets("Main").Range("A2").PasteSpecial Paste:=xlValues Sheets("Main").Calculate Sheets("Filter").Range("A2:M5000").ClearContents sheets("main").Range("I2:T" & Lrow).Copy Sheets("Filter").Range("A2").PasteSpecial Paste:=xlValues Sheets("Filter").Activate Call CopyGrpUp End Sub Sub CopyGrpUp() mycol = "T" For j = Cells(rows.count, mycol).End(xlUp).Row To 2 Step -1 If Cells(j - 1, mycol) = "" Then Cells(j - 1, mycol).Value = Cells(j, mycol) End If next j End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Jim May" wrote in message ... Never Mind Don,, I worked it out.. Thanks for the help Jim "Jim May" wrote: I Have: within Sub FindlRow: ... Lrow = Cells(i, 1).Row Exit For End If Next i End With ActiveSheet.Range("A2:G" & Lrow).Copy Sheets("Main").Range("A2").PasteSpecial Paste:=xlValues CutCopyMode = False With Sheets("Main") .Activate .Calculate .Range("I2").Select End With With Sheets("Filter") .Range("A2:M5000").ClearContents End With ActiveSheet.Range("I2:T" & Lrow).Copy Sheets("Filter").Range("A2").PasteSpecial Paste:=xlValues CutCopyMode = False Sheets("Filter").Activate Range("b2").Select Call CopyGrpUp <<< See below End Sub Sub CopyGrpUp() ' Your code modified... mycol = "T" For j = Cells(Lrow, mycol).End(xlUp).Row To 2 Step -1 " R/t 1004 Occurs here !! If Cells(j - 1, mycol) = "" Then Cells(j - 1, mycol).Value = Cells(j, mycol) End If Next j End Sub But when I run I get R/T 1004 Lrow "Don Guillett" wrote: Sub fillinblanks() mycol = "L" For i = Cells(Rows.Count, mycol).End(xlUp).Row To 2 Step -1 If Cells(i - 1, mycol) = "" Then Cells(i - 1, mycol).Value = Cells(i, mycol) End If Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Jim May" wrote in message ... I need a procedure that will start with say cell L300 (which has a text value) and Copy it into cells L299-L250 that are blank; L249 has a different Cell value and I need to copy it into cells L248-L150 that are blank, etc until Row 2. How would I do that? Do while.. Loop but I can't solve... Any help appreciated |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to make a blank cell to automatically copy what is above | Excel Discussion (Misc queries) | |||
| Copy data in one cell to blank cell immediately below, repeat | Excel Worksheet Functions | |||
| HOW DO I COPY THE LAST NON BLANK CELL IN A COLUMN | Excel Worksheet Functions | |||
| Excel VB-Copy formula down until adjacent cell (left) is blank? | Excel Discussion (Misc queries) | |||
| COPY A CONCATENATE CELL TO BLANK CELL PUTTING IN THE NEXT BLANK C. | Excel Discussion (Misc queries) | |||
Linear Mode
