Record macro range

%3CLINGO-SUB%20id%3D%22lingo-sub-1626574%22%20slang%3D%22en-US%22%3ERecord%20macro%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1626574%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20new%20to%20VBA.%20I%20tried%20recording%20a%20Macro%20for%20a%20particular%20task.%20The%20files%20I%20run%20this%20macro%20will%20have%20the%20range%20that%20keep%20changing%20every%20time.%20Is%20there%20a%20way%20I%20can%20edit%20this%20Macro%20to%20choose%20all%20the%20columns%20that%20is%20no%20empty%3F%20Below%20is%20the%20code%20I%20created%20with%20the%20record%20macro.%20Thank%20you%20in%20advance%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20Smart_biz_report()%0A'%0A'%20Smart_biz_report%20Macro%0A'%20Smart%20Biz%20report%20formating%0A'%0A%0A'%0A%20%20%20%20ActiveWindow.ActivateNext%0A%20%20%20%20ActiveWindow.ActivateNext%0A%20%20%20%20ActiveWindow.ActivateNext%0A%20%20%20%20ActiveWindow.ActivateNext%0A%20%20%20%20Windows(%22Sales%20report%20-%20SBO02371.xlsx%22).Activate%0A%20%20%20%20Columns(%22U%3AU%22).Select%0A%20%20%20%20Selection.Insert%20Shift%3A%3DxlToRight%0A%20%20%20%20Range(%22U1%22).Select%0A%20%20%20%20ActiveCell.FormulaR1C1%20%3D%20%22Category%22%0A%20%20%20%20Range(%22U2%22).Select%0A%20%20%20%20Application.CutCopyMode%20%3D%20False%0A%20%20%20%20ActiveCell.FormulaR1C1%20%3D%20%22%3DRC%5B1%5D%26amp%3BRC%5B2%5D%22%0A%20%20%20%20Range(%22U2%22).Select%0A%20%20%20%20Selection.Copy%0A%20%20%20%20Range(%22T2%22).Select%0A%20%20%20%20Selection.End(xlDown).Select%0A%20%20%20%20Range(%22U1284%22).Select%0A%20%20%20%20Range(Selection%2C%20Selection.End(xlUp)).Select%0A%20%20%20%20ActiveSheet.Paste%0A%20%20%20%20Selection.End(xlUp).Select%0A%20%20%20%20Columns(%22U%3AU%22).Select%0A%20%20%20%20Application.CutCopyMode%20%3D%20False%0A%20%20%20%20Selection.Copy%0A%20%20%20%20Selection.PasteSpecial%20Paste%3A%3DxlPasteValues%2C%20Operation%3A%3DxlNone%2C%20SkipBlanks%20_%0A%20%20%20%20%20%20%20%20%3A%3DFalse%2C%20Transpose%3A%3DFalse%0A%20%20%20%20Application.CutCopyMode%20%3D%20False%0A%20%20%20%20Columns(%22V%3AV%22).Select%0A%20%20%20%20Selection.Insert%20Shift%3A%3DxlToRight%0A%20%20%20%20Range(%22V1%22).Select%0A%20%20%20%20ActiveCell.FormulaR1C1%20%3D%20%22Category%202%22%0A%20%20%20%20Range(%22V2%22).Select%0A%20%20%20%20ActiveCell.FormulaR1C1%20%3D%20_%0A%20%20%20%20%20%20%20%20%22%3DVLOOKUP(RC%5B-1%5D%2C'%5BSales%20report%20-%20Category.xls%5DCategories'!R2C1%3AR224C2%2C2%2CTRUE)%22%0A%20%20%20%20Range(%22V2%22).Select%0A%20%20%20%20Selection.Copy%0A%20%20%20%20Range(%22U2%22).Select%0A%20%20%20%20Selection.End(xlDown).Select%0A%20%20%20%20Range(%22V1284%22).Select%0A%20%20%20%20Range(Selection%2C%20Selection.End(xlUp)).Select%0A%20%20%20%20ActiveSheet.Paste%0A%20%20%20%20Selection.End(xlUp).Select%0A%20%20%20%20Columns(%22V%3AV%22).Select%0A%20%20%20%20Application.CutCopyMode%20%3D%20False%0A%20%20%20%20Selection.Copy%0A%20%20%20%20Selection.PasteSpecial%20Paste%3A%3DxlPasteValues%2C%20Operation%3A%3DxlNone%2C%20SkipBlanks%20_%0A%20%20%20%20%20%20%20%20%3A%3DFalse%2C%20Transpose%3A%3DFalse%0A%20%20%20%20Columns(%22V%3AV%22).Select%0A%20%20%20%20Selection.Replace%20What%3A%3D%220%22%2C%20Replacement%3A%3D%22Other%22%2C%20LookAt%3A%3DxlPart%2C%20_%0A%20%20%20%20%20%20%20%20SearchOrder%3A%3DxlByRows%2C%20MatchCase%3A%3DFalse%2C%20SearchFormat%3A%3DFalse%2C%20_%0A%20%20%20%20%20%20%20%20ReplaceFormat%3A%3DFalse%2C%20FormulaVersion%3A%3DxlReplaceFormula2%0A%20%20%20%20Range(%22V2%22).Select%0A%20%20%20%20ActiveWindow.LargeScroll%20Down%3A%3D1%0A%20%20%20%20Range(%22V27%22).Select%0A%20%20%20%20ActiveWindow.LargeScroll%20Down%3A%3D1%0A%20%20%20%20Range(%22V52%22).Select%0A%20%20%20%20ActiveWindow.LargeScroll%20Down%3A%3D1%0A%20%20%20%20Range(%22V77%22).Select%0A%20%20%20%20ActiveWindow.LargeScroll%20Down%3A%3D1%0A%20%20%20%20Range(%22V102%22).Select%0A%20%20%20%20ActiveWindow.LargeScroll%20Down%3A%3D1%0A%20%20%20%20Range(%22V127%22).Select%0A%20%20%20%20ActiveWindow.LargeScroll%20Down%3A%3D1%0A%20%20%20%20Range(%22V152%22).Select%0A%20%20%20%20ActiveWindow.LargeScroll%20Down%3A%3D1%0A%20%20%20%20Range(%22V177%22).Select%0A%20%20%20%20ActiveWindow.LargeScroll%20Down%3A%3D1%0A%20%20%20%20Range(%22V202%22).Select%0A%20%20%20%20ActiveWindow.LargeScroll%20Down%3A%3D1%0A%20%20%20%20Range(%22V227%22).Select%0A%20%20%20%20ActiveWindow.LargeScroll%20Down%3A%3D1%0A%20%20%20%20Range(%22V252%22).Select%0A%20%20%20%20ActiveWindow.LargeScroll%20Down%3A%3D1%0A%20%20%20%20Range(%22V277%22).Select%0A%20%20%20%20ActiveWindow.LargeScroll%20Down%3A%3D1%0A%20%20%20%20Range(%22V302%22).Select%0A%20%20%20%20ActiveWindow.LargeScroll%20Down%3A%3D1%0A%20%20%20%20Range(%22V327%22).Select%0A%20%20%20%20ActiveWindow.LargeScroll%20Down%3A%3D1%0A%20%20%20%20Range(%22V352%22).Select%0A%20%20%20%20ActiveWindow.LargeScroll%20Down%3A%3D1%0A%20%20%20%20Range(%22V377%22).Select%0A%20%20%20%20ActiveWindow.LargeScroll%20Down%3A%3D1%0A%20%20%20%20Range(%22V402%22).Select%0A%20%20%20%20Selection.End(xlUp).Select%0A%20%20%20%20Rows(%221%3A1%22).Select%0A%20%20%20%20Range(%22V1%22).Activate%0A%20%20%20%20Range(Selection%2C%20Selection.End(xlDown)).Select%0A%20%20%20%20Application.CutCopyMode%20%3D%20False%0A%20%20%20%20Selection.AutoFilter%0A%20%20%20%20Range(%22V1%22).Select%0A%20%20%20%20Selection.AutoFilter%0A%20%20%20%20Selection.End(xlToLeft).Select%0A%20%20%20%20Range(Selection%2C%20Selection.End(xlToRight)).Select%0A%20%20%20%20Range(Selection%2C%20Selection.End(xlDown)).Select%0A%20%20%20%20Application.CutCopyMode%20%3D%20False%0A%20%20%20%20Application.CutCopyMode%20%3D%20False%0A%20%20%20%20ActiveWorkbook.PivotCaches.Create(SourceType%3A%3DxlDatabase%2C%20SourceData%3A%3D%20_%0A%20%20%20%20%20%20%20%20%22'Sales%20report%20-%20SBO02371'!R1C1%3AR1284C30%22%2C%20Version%3A%3D6).CreatePivotTable%20_%0A%20%20%20%20%20%20%20%20TableDestination%3A%3D%22%22%2C%20TableName%3A%3D%22PivotTable6%22%2C%20DefaultVersion%3A%3D6%0A%20%20%20%20ActiveSheet.PivotTableWizard%20TableDestination%3A%3DActiveSheet.Cells(3%2C%201)%0A%20%20%20%20ActiveSheet.Cells(3%2C%201).Select%0A%20%20%20%20Sheets(%22Sheet1%22).Select%0A%20%20%20%20With%20ActiveSheet.PivotTables(%22PivotTable6%22).PivotFields(%22textBox2%22)%0A%20%20%20%20%20%20%20%20.Orientation%20%3D%20xlRowField%0A%20%20%20%20%20%20%20%20.Position%20%3D%201%0A%20%20%20%20End%20With%0A%20%20%20%20With%20ActiveSheet.PivotTables(%22PivotTable6%22).PivotFields(%22Category%202%22)%0A%20%20%20%20%20%20%20%20.Orientation%20%3D%20xlColumnField%0A%20%20%20%20%20%20%20%20.Position%20%3D%201%0A%20%20%20%20End%20With%0A%20%20%20%20Sheets(%22Sales%20report%20-%20SBO02371%22).Select%0A%20%20%20%20ActiveWindow.ScrollColumn%20%3D%202%0A%20%20%20%20ActiveWindow.ScrollColumn%20%3D%203%0A%20%20%20%20ActiveWindow.ScrollColumn%20%3D%204%0A%20%20%20%20ActiveWindow.ScrollColumn%20%3D%205%0A%20%20%20%20ActiveWindow.ScrollColumn%20%3D%206%0A%20%20%20%20ActiveWindow.ScrollColumn%20%3D%207%0A%20%20%20%20ActiveWindow.ScrollColumn%20%3D%208%0A%20%20%20%20Sheets(%22Sheet1%22).Select%0A%20%20%20%20ActiveSheet.PivotTables(%22PivotTable6%22).AddDataField%20ActiveSheet.PivotTables(%20_%0A%20%20%20%20%20%20%20%20%22PivotTable6%22).PivotFields(%22textBox26%22)%2C%20%22Sum%20of%20textBox26%22%2C%20xlSum%0A%20%20%20%20With%20ActiveSheet.PivotTables(%22PivotTable6%22).PivotFields(%22Sum%20of%20textBox26%22)%0A%20%20%20%20%20%20%20%20.NumberFormat%20%3D%20%22%24%23%2C%23%230.00%22%0A%20%20%20%20End%20With%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1626574%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1631802%22%20slang%3D%22en-US%22%3ERe%3A%20Record%20macro%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1631802%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F778847%22%20target%3D%22_blank%22%3E%40Sibujoy%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Sibujoy%2C%3C%2FP%3E%3CP%3ESince%20I%20cannot%20run%20this%20macro%20it%20is%20difficult%20to%20determine%20which%20cells%20you%20are%20referring%20to.%20You%20start%20out%20selecting%20Column%20U%2C%20shifting%20it%20to%20the%20right%2C%20then%20labeling%20the%20new%20U.%20Later%2C%20you%20do%20the%20same%20for%20column%20V.%20Where%20is%20the%20%22blank%22%20column%20at%20that%20you%20want%20to%20select%3F%20Assuming%20I%20understand%20what%20you%20are%20looking%20for%2C%20if%2C%20starting%20with%20Column%20U%2C%20you%20named%20the%20range%20and%20then%20put%20the%20named%20range%20in%20your%20macro%2C%20it%20will%20follow%20the%20named%20range%20when%20performing%20the%20next%20tasks.%20To%20name%20a%20range%20you%20can%20select%20U%20to%20highlight%20the%20column%2C%20then%20in%20the%20upper%20left%20box%20(right%20above%20column%20%22A%22)%20where%20it%20says%20'U1'%20click%20in%20that%20box%20and%20type%20in%20a%20name%20like%20%22START%22%20then%20press%20enter.%20(If%20it%20does%20not%20take%20the%20name%2C%20it%20will%20remove%20it%20and%20put%20U1%20back%20in.)%3C%2FP%3E%3CP%3EIn%20your%20macro%20where%20you%20have%20%22U%3AU%22%20you%20can%20now%20use%20%22START%22.%20As%20another%20column%20is%20placed%20before%20column%20U%2C%20the%20START%20range%20will%20now%20be%20in%20column%20V%20and%20so%20forth.%20To%20see%20how%20it%20is%20working%2C%20you%20can%20highlight%20the%20START%20row%20in%20some%20fashion%20and%20then%20when%20in%20VBA%2C%20under%20debug%2C%20STEP%20INTO%20-%26nbsp%3B%20F8%20and%20run%20your%20macro%20one%20step%20at%20a%20time%20to%20see%20how%20this%20works.%20Let%20me%20know%20if%20this%20helps.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1640129%22%20slang%3D%22en-US%22%3ERe%3A%20Record%20macro%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1640129%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F628423%22%20target%3D%22_blank%22%3E%40SqueakySneakers%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20will%20try%20and%20update%20my%20findings%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I am new to VBA. I tried recording a Macro for a particular task. The files I run this macro will have the range that keep changing every time. Is there a way I can edit this Macro to choose all the columns that is no empty? Below is the code I created with the record macro. Thank you in advance

 

 

Sub Smart_biz_report()
'
' Smart_biz_report Macro
' Smart Biz report formating
'

'
    ActiveWindow.ActivateNext
    ActiveWindow.ActivateNext
    ActiveWindow.ActivateNext
    ActiveWindow.ActivateNext
    Windows("Sales report - SBO02371.xlsx").Activate
    Columns("U:U").Select
    Selection.Insert Shift:=xlToRight
    Range("U1").Select
    ActiveCell.FormulaR1C1 = "Category"
    Range("U2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=RC[1]&RC[2]"
    Range("U2").Select
    Selection.Copy
    Range("T2").Select
    Selection.End(xlDown).Select
    Range("U1284").Select
    Range(Selection, Selection.End(xlUp)).Select
    ActiveSheet.Paste
    Selection.End(xlUp).Select
    Columns("U:U").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Columns("V:V").Select
    Selection.Insert Shift:=xlToRight
    Range("V1").Select
    ActiveCell.FormulaR1C1 = "Category 2"
    Range("V2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-1],'[Sales report - Category.xls]Categories'!R2C1:R224C2,2,TRUE)"
    Range("V2").Select
    Selection.Copy
    Range("U2").Select
    Selection.End(xlDown).Select
    Range("V1284").Select
    Range(Selection, Selection.End(xlUp)).Select
    ActiveSheet.Paste
    Selection.End(xlUp).Select
    Columns("V:V").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("V:V").Select
    Selection.Replace What:="0", Replacement:="Other", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    Range("V2").Select
    ActiveWindow.LargeScroll Down:=1
    Range("V27").Select
    ActiveWindow.LargeScroll Down:=1
    Range("V52").Select
    ActiveWindow.LargeScroll Down:=1
    Range("V77").Select
    ActiveWindow.LargeScroll Down:=1
    Range("V102").Select
    ActiveWindow.LargeScroll Down:=1
    Range("V127").Select
    ActiveWindow.LargeScroll Down:=1
    Range("V152").Select
    ActiveWindow.LargeScroll Down:=1
    Range("V177").Select
    ActiveWindow.LargeScroll Down:=1
    Range("V202").Select
    ActiveWindow.LargeScroll Down:=1
    Range("V227").Select
    ActiveWindow.LargeScroll Down:=1
    Range("V252").Select
    ActiveWindow.LargeScroll Down:=1
    Range("V277").Select
    ActiveWindow.LargeScroll Down:=1
    Range("V302").Select
    ActiveWindow.LargeScroll Down:=1
    Range("V327").Select
    ActiveWindow.LargeScroll Down:=1
    Range("V352").Select
    ActiveWindow.LargeScroll Down:=1
    Range("V377").Select
    ActiveWindow.LargeScroll Down:=1
    Range("V402").Select
    Selection.End(xlUp).Select
    Rows("1:1").Select
    Range("V1").Activate
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.AutoFilter
    Range("V1").Select
    Selection.AutoFilter
    Selection.End(xlToLeft).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "'Sales report - SBO02371'!R1C1:R1284C30", Version:=6).CreatePivotTable _
        TableDestination:="", TableName:="PivotTable6", DefaultVersion:=6
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    Sheets("Sheet1").Select
    With ActiveSheet.PivotTables("PivotTable6").PivotFields("textBox2")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable6").PivotFields("Category 2")
        .Orientation = xlColumnField
        .Position = 1
    End With
    Sheets("Sales report - SBO02371").Select
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 8
    Sheets("Sheet1").Select
    ActiveSheet.PivotTables("PivotTable6").AddDataField ActiveSheet.PivotTables( _
        "PivotTable6").PivotFields("textBox26"), "Sum of textBox26", xlSum
    With ActiveSheet.PivotTables("PivotTable6").PivotFields("Sum of textBox26")
        .NumberFormat = "$#,##0.00"
    End With
End Sub

 

 

 

2 Replies
Highlighted

@Sibujoy 

Hi Sibujoy,

Since I cannot run this macro it is difficult to determine which cells you are referring to. You start out selecting Column U, shifting it to the right, then labeling the new U. Later, you do the same for column V. Where is the "blank" column at that you want to select? Assuming I understand what you are looking for, if, starting with Column U, you named the range and then put the named range in your macro, it will follow the named range when performing the next tasks. To name a range you can select U to highlight the column, then in the upper left box (right above column "A") where it says 'U1' click in that box and type in a name like "START" then press enter. (If it does not take the name, it will remove it and put U1 back in.)

In your macro where you have "U:U" you can now use "START". As another column is placed before column U, the START range will now be in column V and so forth. To see how it is working, you can highlight the START row in some fashion and then when in VBA, under debug, STEP INTO -  F8 and run your macro one step at a time to see how this works. Let me know if this helps.

Highlighted

@SqueakySneakers 

 

Thank you will try and update my findings