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

[S.H.A.D.O.]

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.

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

Thanks in advance,

 

[Rick Rothstein]

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.

    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?

 

[S.H.A.D.O.]

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.

 

[Jonmo1]

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

 

[JoeMo]

Try:

ColData = Sheets("Calculation").Range("I6",Sheets("Calculation").Cells(Rows.Count,"I").End(xlUp)).Value

 

[Rick Rothstein]

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..

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.

 

[S.H.A.D.O.]

Hi Joe,

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

 

[JoeMo]

Hi Joe,

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

Sorry,thought you wanted to store values. See Rick's response in post #6.

 

[S.H.A.D.O.]

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.

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.

 

[Rick Rothstein]

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...

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.