Macro Help-Autofill Issue?

[johnnydop]

I have two columns, one with text (col a) and one with a date stored as text (col b). The code below creates a new column (col c) where it places the text from col a and the "date" from col b together seperated by two dashes, "--".

The problem i have is the data set i'm using will not always be the same number of rows. As you can see below, the following line of code (Range("B6:B32").Select) uses a row range of B6-B32 because that was the amount of rows in the file when i recorded the macro.
i.e. If the number or rows is less then 32, the code applies to rows 32 and and beyond, and if the number of rows is more then 32, it will miss anything after row 32.

How can i update the code so it will apply that code to what ever number of consecutive rows exist, or the equivalent of a "control+shift+down"?

Sub formatdate()
'
' formatdate Macro
' Macro recorded 8/13/2009 by
'
'
Worksheets("Position Rec").Select
Range("B6").Select
ActiveCell.FormulaR1C1 = _
"=RC[4]&""--""&MID(RC[22],5,2)&""/""&RIGHT(RC[22],2)&""/""&LEFT(RC[22],4)"
Range("B6").Select
Selection.AutoFill Destination:=Range("B6:B32")
Range("B6:B32").Select
Selection.Copy
Range("F6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("B:B").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("B6").Select
MsgBox "Done!"
End Sub

Thanks!

 

[Norie]

Something like this perhaps.

    With Worksheets("Position Rec")
        LastRow = .Range("A" & Rows.Count).End(xlUp).Row
        With .Range("B6:B" & LastRow)
            .FormulaR1C1 = "=RC[4]&""--""&MID(RC[22],5,2)&""/""&RIGHT(RC[22],2)&""/""&LEFT(RC[22],4)"
            .Copy
            .Offset(, 4).PasteSpecial Paste:=xlPasteValues
            .EntireColumn.ClearContents
        End With
    End With
 
    MsgBox "Done!"

 

[taigovinda]

What he said

 

[johnnydop]

So this code will replace all of the code in my post?

Something like this perhaps.

    With Worksheets("Position Rec")
        LastRow = .Range("A" & Rows.Count).End(xlUp).Row
        With .Range("B6:B" & LastRow)
            .FormulaR1C1 = "=RC[4]&""--""&MID(RC[22],5,2)&""/""&RIGHT(RC[22],2)&""/""&LEFT(RC[22],4)"
            .Copy
            .Offset(, 4).PasteSpecial Paste:=xlPasteValues
            .EntireColumn.ClearContents
        End With
    End With
 
    MsgBox "Done!"