Excel Macro basics

[Gazzr]

Hi all,
I am new to macros and would hope that somebody could assist.
I have an exported csv file from a database that i need to reformat and would like to create a macro to aid this.

The part of the task that i am struggling with is the following:
(This example has only 20 rows of data)
I insert a new column between A and B
I insert a formula into B1 (="Item No: "&a1)
I copy this down to B20
I then copy B1:B20 and use paste special>values into A1:A20
I then delete Column B

If i record a macro, it obviously only works up to row 20, but the spreadsheet "grows" each time i need to do this and i would like the macro to automatically perform the action for all the populated rows present in column A.
I have had a look at the VBA code and can see each time it refers to the cell range(s). I just don't know what i need to insert in place of these absolute references.

Ta in advance.

[cassy34]

Range("A65536").End(xlup).Row will give you the number of the last used row in col A

[Gazzr]

Thanks for the info. Could you please assist me in grafting that code into the following example:

Sub Macro2()
'
' Macro2 Macro
'

'
Range("B1").Select
ActiveCell.FormulaR1C1 = "=""Item No: ""&RC[-1]"
Range("B1").Select
Selection.AutoFill Destination:=Range("B1:B20"), Type:=xlFillDefault
Range("B1:B20").Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("B:B").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("B1").Select
End Sub

[akimba]

I know this is a bit old now but when recording macros instead of highlighting the range with the cursor Range(B1:B20)

Hold down ctrl and shift and press down, this will give you the following code in your macro instead

Range(Selection, Selection.End(xlDown)).Select

Which will then work with however many rows you have ;-)

[Gazzr]

Akimba, thanks. I never did solve this problem.

I have had another look at this and used your advice. I now have the following. My only problem is highlighted in red:

Range("B1").Select
ActiveCell.FormulaR1C1 = "=""Item: ""&RC[-1]"
Range("B1").Select
Selection.AutoFill Destination:=Range("B1:B20"), Type:=xlFillDefault
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents

End Sub

I need to copy the formula down as many rows as column A has. In this case, there were 20 rows in A so i copied from B1:B20. I need to automatically ascertain how many rows A has. I think Cassiy34 has the answer but i do not know how to use the code he has written to achieve what i require.

Thanks for the help.

[Over Carl]

Haven't messed with these for a good few years but I'm guessing below may be what you need?

Sub Macro2()
'
' Macro2 Macro
'

'
Range("B1").Select
ActiveCell.FormulaR1C1 = "=""Item No: ""&RC[-1]"
Range("B1").Select
Selection.AutoFill Destination:=Range("A65536").End(xlup).Row,Type:=xlFillDefault
Range("B1:B20").Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("B:B").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("B1").Select
End Sub

[ap0c]

My attempt:

Range("B1").Select
ActiveCell.FormulaR1C1 = "=""Item: ""&RC[-1]"
Range("B1").Select
Selection.AutoFill Destination:=Range("B1:B" & Range('A65536').End(xlUp).Row), Type:=xlFillDefault
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents

End Sub

[Gazzr]

Sorry fella's - neither work...!

Basically what i wish to do is convert a column of numbers:
Column A
(A1) 125
(A2) 237
(A3) 328
(A4) 764

into the following column of text and original numbers {in the same column}
Column A
(A1) Item number: 125
(A2) Item number: 237
(A3) Item Number: 328
(A4) Item Number: 764

[ap0c]

I think I have sussed what you were after:

macro.zip

[ap0c]

New version with better error checking!

Bored at work sorry

macro.zip

[akimba]

Hi

Ap0c seems to have a good answer there

I use a Function I wrote called LastRow t opredict the last row of data, I have copied it just incase you need it in the future


'AlanKimber 18/06/2010
'Works out the last row of data in a column of a worksheet
'if there is a blank row in the data then this will be picked up
'sColumn = the column you want to look in
'iStart = the Starting cell i.e. might not want to start at 1 (A1 for instance)
'sWorksheet = worksheet you want to search in
Function LastRow(sColumn, iStart, sWorksheet) As Integer
Dim lrn
lrn = iStart
Do Until Worksheets(sWorksheet).Range(sColumn & Trim(Str(lrn))) = ""
lrn = lrn + 1
Loop
LastRow = lrn - 1
End Function

Add this to your VBA and call it in the Sub like so

Selection.AutoFill Destination:=Range("B1:B" & CSTR(LastRow("A",1,"Sheet1")), Type:=xlFillDefault

[Gazzr]

Thanks a lot fellas. I now have the following:
macro2.rar
I export a csv from my database, copy and paste the first three columns into the "macro.xls" - click the button and hey presto! All are converted. (Text added, columns resized) I then resave as a csv, delete all the rows that i do not need then send the csv to a label printer. Brilliant.

Thanks again.