Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Copy Up until Non-Blank Cell encountered
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 10,124
Default 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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 477
Default 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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 477
Default 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


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 10,124
Default 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 With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 10,124
Default Copy Up until Non-Blank Cell encountered
Your problem appears? to be at the start with "within". What are you trying
to do??? What I sent was intended to determine the last row in column
T...... and fill in the blanks in col T from the bottom up, as requested.
What comes before that needs a re-think.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jim May" wrote in message
...
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 With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to make a blank cell to automatically copy what is above Lukshmi B Excel Discussion (Misc queries) 1 September 4th 07 06:03 PM
Copy data in one cell to blank cell immediately below, repeat Jeff Excel Worksheet Functions 1 May 19th 06 07:12 PM
HOW DO I COPY THE LAST NON BLANK CELL IN A COLUMN Needles Excel Worksheet Functions 2 October 16th 05 06:39 PM
Excel VB-Copy formula down until adjacent cell (left) is blank? Tony P. Excel Discussion (Misc queries) 1 May 18th 05 06:11 PM
COPY A CONCATENATE CELL TO BLANK CELL PUTTING IN THE NEXT BLANK C. QUEST41067 Excel Discussion (Misc queries) 1 January 15th 05 09:29 PM


All times are GMT +1. The time now is 12:26 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement