Using cell value to change range size

[Defski]

I am looking for help to make the following happen.

Say like I have a range that is from A20:C50, how can I make it so if a value in cell lets say cell A5 was "8" my new range would be A28:C50.

Ultimately I would like to be able to take the remaining data and copy and paste it into another sheet.

I assume this requires a VBA solution???


Thanks,

 

[mikerickson]

the formula =INDEX(A:A, 20+A5, 1):C50 will return the range that you descibe, but if you want to copy and paste it, you would need VBA

Range(Cells(Range("A5").Value+10,1), Range("C50")).Select

 

[Defski]

Thanks the VBA code did work! Although I have another question for you if you don't mind.

Would there be away for the code to find the end of the range automatically, because sometimes the end of the ranges is a few rows shorter or longer.

Cheers

 

[mikerickson]

Yes there is a way, but it depends on what you mean by the end of the range.

The last cell in a column (or a row) that is filled, or the cell before the first blank, or some other definition of "end of range"?

 

[Defski]

I Guess I would mean the last cell in a column or row. What I mean is the range sometimes varies, but the column is always the same although sometimes the range will contain a few more or a few less rows of data, if that makes sense.

 

[mikerickson]

To find the last cell in column A, I would use code like this.

Dim LastA as Range

Set LastA = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlup)

Then to get that to go all the way to column C

Dim LastA as Range

Set LastA = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlup)

LastA.Resize(,3).Select

Note that this will include cells that contain formulas which evaluate to "" in the selected range.

 

[Defski]

Sorry for my lack of knowledge here, but could you please combine the code you gave me in your first answer with this to make it work. I tried just cutting and pasting it and it did not work.

Thanks again for taking the time to help me!

 

[Defski]

Ok I have been able to get it to find the last row of the range now using your code but it is no longer finding the starting point of the range.

This is what I have.

Sub test()
range(Cells(range("D24").Value + 30, 1), range("C50")).Select
Dim LastA As range
Set LastA = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp)
LastA.Resize(, 3).Select
End Sub

 

[My Aswer Is This]

I have been following this thread but not getting involved.

Would you mind telling me what your ultimate goal is.
Don't say trying to find cell with value of 8 and then changing ranges etc.''I would just like to know what is your ultimate goal and maybe we could provide a way to accomplish your ultimate goal.

I know you did say this:

Ultimately I would like to be able to take the remaining data and copy and paste it into another sheet.
​

But copy it to what sheet give us sheet name and paste it where in other sheet.

 

[Defski]

Basically I am wanting to take the ending data that is leftover in a range select it and copy and move the data to a new sheet to start a new cycle.

So say like I had a starting range of data from B20:C50 as inventory.

When this inventory is used up the only data that would be important is the remaining data left, maybe something like B46:C50. Like I said before sometimes the last row and very a few more or less.

Now when I start a new cycle I need the remaining data to start the new cycle.

As far as new sheet name I am using "New Cycle" and I am still playing with format so the pasted range is not really all that important right now but currently I am pasting to C14

I hope this make sense.

I really appreciate your help.