changing range in recorded macro

B

bearywell

I recorded this macro and since I am new to VB I am unable to modify
this code. The range is not always going to be A1:A123 so I need to
be able to change code to select last row in column A. I know I could
cheat and just put in A1:A1000 because it is a list of account codes
and would never be more than 1000. But this leaves me with 0 values
all the way to 1000. Doesn't look professional and this is for my new
boss. I have looked on the internet and found lots of code to select
range from a1 to end but I don't know where to put it.

Recorded Macro:

Sub Concant_delete()
'
' Concant_delete Macro
' created by Dee , 6/10/09
'

'
ActiveCell.Cells.Select
With Selection
.VerticalAlignment = xlTop
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveCell.Cells.EntireColumn.AutoFit
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.EntireColumn.Insert
ActiveCell.Offset(0, -1).Columns("A:A").EntireColumn.Select
Selection.TextToColumns Destination:=ActiveCell,
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(11, 1)),
TrailingMinusNumbers:=True
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.EntireColumn.Insert
ActiveCell.FormulaR1C1 = "=CONCATENATE(LEFT(RC[-1],4),RIGHT(RC[-1],
3))"
Selection.AutoFill Destination:=ActiveCell.Range("A1:A123")
ActiveCell.Range("A1:A123").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(0, -1).Range("A1").Select
Application.CutCopyMode = False
Selection.EntireColumn.Delete
Columns("H:H").Select
Selection.Cut
Columns("G:G").Select
Selection.Insert Shift:=xlToRight
Range("I1").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-2]="""",RC[-1],-RC[-1])"
Range("I1").Select
Selection.AutoFill Destination:=Range("I1:I122")
Range("I1:I122").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Columns("G:G").Select
Application.CutCopyMode = False
Selection.Cut
Columns("I:I").Select
Selection.Insert Shift:=xlToRight
Columns("C:H").Select
Selection.EntireColumn.Delete
ActiveCell.Cells.EntireColumn.AutoFit

End Sub

I know this is a simple fix but I can't spend more time reading code
on the internet since I am a novice and most of it doesn't make since
anyway. :) Any help would be appreciated.
 
L

Luke M

Defined last row used, and then use that throughout rest of macro to define
range.

Sub Concant_delete()
'
' Concant_delete Macro
' created by Dee , 6/10/09
'
xRow = Range("A:A").SpecialCells(xlCellTypeLastCell).Row

ActiveCell.Cells.Select
With Selection
.VerticalAlignment = xlTop
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveCell.Cells.EntireColumn.AutoFit
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.EntireColumn.Insert
ActiveCell.Offset(0, -1).Columns("A:A").EntireColumn.Select
Selection.TextToColumns Destination:=ActiveCell, DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(11, 1)),
TrailingMinusNumbers:=True
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.EntireColumn.Insert
ActiveCell.FormulaR1C1 = "=CONCATENATE(LEFT(RC[-1],4), _
RIGHT(RC[-1],3))"
Selection.AutoFill Destination:=ActiveCell.Range("A1:A" & xRow)
ActiveCell.Range("A1:A" & xRow).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveCell.Offset(0, -1).Range("A1").Select
Application.CutCopyMode = False
Selection.EntireColumn.Delete
Columns("H:H").Select
Selection.Cut
Columns("G:G").Select
Selection.Insert Shift:=xlToRight
Range("I1").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-2]="""",RC[-1],-RC[-1])"
Range("I1").Select
Selection.AutoFill Destination:=Range("I1:I" & xRow)
Range("I1:I" & xRow).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Columns("G:G").Select
Application.CutCopyMode = False
Selection.Cut
Columns("I:I").Select
Selection.Insert Shift:=xlToRight
Columns("C:H").Select
Selection.EntireColumn.Delete
ActiveCell.Cells.EntireColumn.AutoFit

End Sub
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


bearywell said:
I recorded this macro and since I am new to VB I am unable to modify
this code. The range is not always going to be A1:A123 so I need to
be able to change code to select last row in column A. I know I could
cheat and just put in A1:A1000 because it is a list of account codes
and would never be more than 1000. But this leaves me with 0 values
all the way to 1000. Doesn't look professional and this is for my new
boss. I have looked on the internet and found lots of code to select
range from a1 to end but I don't know where to put it.

Recorded Macro:

Sub Concant_delete()
'
' Concant_delete Macro
' created by Dee , 6/10/09
'

'
ActiveCell.Cells.Select
With Selection
.VerticalAlignment = xlTop
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveCell.Cells.EntireColumn.AutoFit
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.EntireColumn.Insert
ActiveCell.Offset(0, -1).Columns("A:A").EntireColumn.Select
Selection.TextToColumns Destination:=ActiveCell,
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(11, 1)),
TrailingMinusNumbers:=True
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.EntireColumn.Insert
ActiveCell.FormulaR1C1 = "=CONCATENATE(LEFT(RC[-1],4),RIGHT(RC[-1],
3))"
Selection.AutoFill Destination:=ActiveCell.Range("A1:A123")
ActiveCell.Range("A1:A123").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(0, -1).Range("A1").Select
Application.CutCopyMode = False
Selection.EntireColumn.Delete
Columns("H:H").Select
Selection.Cut
Columns("G:G").Select
Selection.Insert Shift:=xlToRight
Range("I1").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-2]="""",RC[-1],-RC[-1])"
Range("I1").Select
Selection.AutoFill Destination:=Range("I1:I122")
Range("I1:I122").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Columns("G:G").Select
Application.CutCopyMode = False
Selection.Cut
Columns("I:I").Select
Selection.Insert Shift:=xlToRight
Columns("C:H").Select
Selection.EntireColumn.Delete
ActiveCell.Cells.EntireColumn.AutoFit

End Sub

I know this is a simple fix but I can't spend more time reading code
on the internet since I am a novice and most of it doesn't make since
anyway. :) Any help would be appreciated.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro to fill to next field with data 4
Macro should Save and Overwrite file 1
Different Results from the Same Macro 2
Run-time error 1004 paste method of worksheet class failed 3
File Name changes with dates and Deleting Rows of Zeros 0
Complie Syntax Error 6
Data samples 3
Cleanup this macro please 3

Top