Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
changing range in recorded macro
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
changing range in recorded macro
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" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
Recorded single column insert in macro. Macro inserts two not one | Excel Programming | |||
Recorded Macro | Excel Programming | |||
Creating a macro which presses a button containing a recorded macro | Excel Programming | |||
changing a recorded macro - date problem.... | Excel Programming |