Why can't I get it to start at "I6"?

S.H.A.D.O.

Well-known Member
Joined
Sep 6, 2005
Messages
1,915
Good evening,

I would like the data range to start at cell "I6" but for some reason what I have tried will not allow me do do this, I know it is probably me being a bit slow, it has been a VERY long week so far.

Code:
    Dim ColData As Integer
    Dim x As Integer
    ColData = Sheets("Calculation").Range("I6" & Rows.Count).End(xlUp).Row: x = 9

Thanks in advance,
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
38,154
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Good evening,

I would like the data range to start at cell "I6" but for some reason what I have tried will not allow me do do this, I know it is probably me being a bit slow, it has been a VERY long week so far.

Code:
    Dim ColData As Integer
    Dim x As Integer
    ColData = Sheets("Calculation").Range("I6" & Rows.Count).End(xlUp).Row: x = 9

Thanks in advance,
What exactly are you trying to store in the ColData variable? The row number of the last used cell in Column I or the values in the range starting in I6 and going down to the last used cell in that column? Or something else?
 
Upvote 0

S.H.A.D.O.

Well-known Member
Joined
Sep 6, 2005
Messages
1,915
Hello Rick,

I am trying to store the range starting in cell "I6" and going down to the last used cell in that column.

Thanks for the reply.
 
Upvote 0

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
ColData = Sheets("Calculation").Range("I6" & Rows.Count).End(xlUp).Row

Let's say you're in XL2003 just for easier reference.
XL2003 has 65536 rows
So Rows.Count = 65536

Range("I6" & Rows.Count)
Range("I6" & 65536)
"I6" & 65536 = I665536
Range("I665536")

There is no such row as 665536

That should just be
ColData = Sheets("Calculation").Range("I" & Rows.Count).End(xlUp).Row
 
Upvote 0

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
18,144
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
ADVERTISEMENT
Try:
Code:
ColData = Sheets("Calculation").Range("I6",Sheets("Calculation").Cells(Rows.Count,"I").End(xlUp)).Value
 
Upvote 0

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
38,154
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
I am trying to store the range starting in cell "I6" and going down to the last used cell in that column.
In that case, then first you need to declare ColData as a Range, not a numeric value (by the way, you should use Long, not Integer for you numeric variable data type). Second, you need two cell references to set the data range. Try this..
Code:
Dim ColData As Range
Set ColData = Sheets("Calculation").Range("I6", Sheets("Calculation").Cells(Rows.Count, "I").End(xlup))

Edit Note: I just saw JoeMo's post... I interpreted your statement "store the range" as meaning you wanted to set a reference to that range whereas he interpreted it as you wanted to create an array in memory that contained the values from that range. Your wording was not clear, but depending on what you actually wanted, one of our posts should satisfy what you are after.
 
Last edited:
Upvote 0

S.H.A.D.O.

Well-known Member
Joined
Sep 6, 2005
Messages
1,915
ADVERTISEMENT
Hi Joe,

Thanks for the reply.
No, that didn't work, it gives me a Run-time error '13'.
 
Upvote 0

S.H.A.D.O.

Well-known Member
Joined
Sep 6, 2005
Messages
1,915
Thank you guys for your time and input. Here is the full code I am using. It works well except for the fact that in the sheet Calculation and in cells I1:I5 I have data that I don't want copied across into the sheet where the button for the Macro is run from.

Code:
Sub Extract_Data()
    Dim c As Range
    Dim ColData As Range
    Dim x As Integer
    Set ColData = Sheets("Calculation").Range("I6", Sheets("Calculation").Cells(Rows.Count, "I").End(xlUp)): x = 9
    Rows("9:750").Delete Shift:=xlUp
    For Each c In Sheets("Calculation").Range("I:I" & ColData)
        If c.Value > 0 Then
            c.EntireRow.Copy Worksheets("Customer Quotation").Range("A" & x)
            x = x + 1
        End If
    Next c
    Range("B9:I650").Sort Key1:=Range("C9"), Order1:=xlAscending
    Range("B2").Select
End Sub

Thanks in advance.
 
Upvote 0

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
38,154
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
I think you might get to an answer faster if you explain, in detail, what your existing data looks like and what you are attempting to do with it. Just posting non-working code does not really tell us enough about what you hope to accomplish (mainly because it is non-working). I will tell you that what I have highlighted in red below makes no sense...

Code:
For Each c In Sheets("Calculation").Range([B][COLOR="#FF0000"]"I:I" & ColData[/COLOR][/B])

you cannot concatenate a text string ("I:I") with a range object (ColData), so your intent is unclear.
 
Upvote 0

Forum statistics

Threads
1,195,588
Messages
6,010,602
Members
441,558
Latest member
lambierules

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top