Excel Macro - Dynamic Column Selection Solved/Closed

Question

Hello,

I am just beginning to learn Excel macros. I was working on a sheet wherein I have to select a column dynamically, based on just the column header name. Let me explain.

Say there is an Excel Sheet which contains the following columns (from A1 to D1):

RecNo      Cust_ID        Cust_Name       Contact_Num

Below these, there is customer data which is present. Now, I want to extract just the Cust_ID (from Column B) and Cust_Name (from Column C) for all the records.

However, the problem is that it is not necessary that Cust_ID would always be in Column B. Though the heading would always be Cust_ID, its location could change to Column C in some other sheet that has been generated and Cust_Name could instead have been present in Column B (the two columns that need to be extracted have been interchanged).

Using the match function, I am able to determine the position of the column, but after that I am not able to select that column. The function I have used to determine the column number is given below:

WorksheetFunction.Match("Cust_ID", Range("A1:D1"), 0)

Can someone please help?

Pankaj · Accepted Answer

I have created something like this.

Consider this:

The last row of the spreadsheet should have values 1 & 0. make a sweep through the bottom row, select all 1s and hide all 0s.

If you need more help on code, reply.

AM · Answer

'   Determining datasheet range.
    Dim lngLastRow As Long
    Dim lngLastCol As Long
    Dim varDataSelect As Variant
    
'   Select rows and columns range dynamically.
    Selection.End(xlDown).Select
    lngLastRowNum = ActiveCell.Row
    Selection.End(xlToRight).Select
    lngLastCol = ActiveCell.Column
    
    'Name of worksheet "MonthlyReceivedClaims_Data" start at Row 1 Column 1
    'and end at ":R" & lngLastRowNum & "C" & lngLastCol
    varDataSelect = "MonthlyReceivedClaims_Data!R1C1:R" & lngLastRowNum & "C" & lngLastCol
    
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        varDataSelect).CreatePivotTable _
        TableDestination:="", TableName:="PivotTable1", DefaultVersion:= _
        xlPivotTableVersion10
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)

'Also it helps to use the record macro function under Tools.
'Click record.  Make the report or pivot table like you want it. Then click stop record and edit code as needed.

Excel Macro - Dynamic Column Selection

2 responses

Similar discussions

Subscribe To Our Newsletter!