• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copy Column until Cell Value = Text

J

jgj1988

New Member
Im trying to copy cells in column B, at first I was copying until the cell was blank and used this code.


With Range("b21")

Range(.Cells(1, 1), .End(xlDown)).copy Destination:=Worksheets("Copy").Range("e3")


End With


But in some of the files that use this macro, there are spaces in between cell values. The range I am looking for will always start at cell B21 and always end with the Cell Text of "Total" in column B.


Does anyone know how I can change this to copy every cell in column b from cell B21 down to a cell that says "Total"? Is it possible to ignore blank cells when doing this? If not I can remove blank cells at the end of my code


Thanks,


James
 
Hi, jgj1988!

Try putting things upside down:

Range(Range("B21"), Cells(ActiveSheet.Rows.Count, 2).End(xlUp)).Copy Destination:=Worksheets("Copy").Range("E3")

Regards!
 
Hi jgj198!


Try this one too...

[pre]
Code: 
FindTotal = WorksheetFunction.Match("Total", [B:B], False)
Range("B21:B" & FindTotal).SpecialCells(2, 23).Copy Sheets("Copy").[E3]
[/pre]

Regards,

Deb
 
Thanks Deb and SirJB7,


What you said worked perfectly, but now I am trying to set a range between two cells based on the two terms "total current scenario" & "total proposed scenario". Here's what I have come up with but I am getting an error "Range of Global Object Failed":


findtotal = WorksheetFunction.Match("Total Current Scenario", [b:b], False)

Range("B21:B" & findtotal).copy Destination:=Worksheets("copy").Range("e3")


findtotalz = WorksheetFunction.Match("Total Current Scenario", [a:a], False) + 1

Findend = WorksheetFunction.Match("Total Proposed Scenario", [c:c], False)


Range(findtotalz & Findend).FormulaR1C1 = "=RC[+1]& RC[+2]"-------this is the line that fails.


Thanks again,


James
 
I played around with this for a while and was able to come up with an answer:


Findtotalz = WorksheetFunction.Match("Total Current Scenario", [c:c], False)

findend = WorksheetFunction.Match("Total Proposed Scenario", [c:c], False)


Range("a" & Findtotalz, "a" & findend).FormulaR1C1 = "=RC[+1]& RC[+2]"


Figured I would post the answer in case some one else had the same question.


Thanks,

James
 
Hi, jgj1988!

Glad you solved it. Thanks for your feedback sharing your solution and for your kind words too. And welcome back whenever needed or wanted.

Regards!
 
You must log in or register to reply here.
Back
Top