Using LastRow properly, or setting range first

[hg1027]

I've been working on a macro to convert estimates to a usable form, and so far I've been using LastRow for Autofill functions. For example:

    'Makes column A a compilation of K, L, M
    Range("A2").Value = "=RC[10]&"" ""&RC[11]&"" ""&RC[12]"
    Range("A2").Select
    LastRow = Range("B" & Rows.Count).End(xlUp).Row - 1
    Selection.AutoFill Destination:=Range("A2").Resize(LastRow), Type:=xlFillDefault

That works fine, but I'm having trouble translating that to work with a text to columns bit (just before the above):

'Text to Columns on column A to remove excess and prettify
    Range("A2:A5000").Select
    Selection.TextToColumns Destination:=Range("J2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
        Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _
       :=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True

I tried

    Range("A2").Select
    LastRow = Range("B" & Rows.Count).End(xlUp).Row - 1

but that just does the first row (2) and not the rest of the range.

I know the way I have it now (I should never get a file with over 500 lines) will work, but I also know it's not the best way. Is there a way to use LastRow with my text to columns?

 

[VoG]

Does this work?

Range("B" & Rows.Count).End(xlUp).Row.TextToColumns Destination:=Range("J2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
        Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _
       :=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True

 

[njimack]

Without knowing exactly what you're doing, I don't know if this would be preferable, but I usually use the following for writing formulae/text to a range of cells...

LastRow = Range("B" & Rows.Count).End(xlUp).Row - 1
Range("A2:A" & LastRow).FormulaR1C1 = "=RC[10]&"" ""&RC[11]&"" ""&RC[12]"

For the text to columns, I would use:

Range("A2:A" & Last_Row).TextToColumns Destination:=Range("J2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
        Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _
       :=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True

 

[hg1027]

njimack - your text to columns section doesn't work, throws a debug. The autofill part does though.

vogii - yours gives "invalid qualifier" at

Range("B" & Rows.Count).End(xlUp).Row.

I guess text to columns uses a different method of identifying the range that autofill? I'll have a look at a with statement Peter_SS sent me yesterday.

 

[njimack]

njimack - your text to columns section doesn't work, throws a debug. The autofill part does though.

There was a typo - replace "Last_Row" with "LastRow"

I've just tested and this works for me.

 

[hg1027]

Works great, thanks neil