Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.
Public Sub DynamicRangeDatasourceIndexOnly() 'It re-sets the entire range the datasources columns 'that are pulled from the Green Package and used in client reports. That is, this worksheets holds 'the value that is on the report, the lookup values that are actually used and which columns the 'data is pulled from. This worksheet is vital in being correct as it is used in data validation! On Error GoTo ErrorHandler 'If user adds a new category, this dynamically determines what the 'new ranges will be. Need to lock the column headers just to be sure. Dim strRange As Range Dim strAddress As String Dim varName As Variant Dim i As Integer Dim intEnd As Integer Dim intRowNum As Integer Dim intAddressNum As Integer Dim rngVarName As Range 'First, find where the data source area starts ThisWorkbook.Worksheets("Datasource").Activate With ThisWorkbook.Worksheets("DataSource").Range("H:H") For Each varName In Array("ss_IDXCurrency", "ss_IDXCountry", "ss_IDXSector", _ "ss_IDXSectorbreakdown", "ss_IDXQuality", "ss_IDXQualitybreakdown") Set strRange = .Find(varName, LookIn:=xlValues) strAddress = strRange.Address Debug.Print strAddress 'Initialize loop counter to seed its value i = 1 'Loop through the fields until a blank row is hit, this is the end of the range Do Until .Range(strAddress).Offset(i, 0) = "" i = i + 1 Loop intRowNum = .Range(strAddress).Row 'Row for varName address intAddressNum = intRowNum + 2 'Start range below headers for group intEnd = (intRowNum + i) - 1 'Last row for end of range .Range("A" & intAddressNum & ":E" & intEnd).Name = varName 'Range name in workbook 'Sort data With ThisWorkbook.Worksheets("DataSource").Range(varName) .Select .Sort Key1:=Range(varName).Cells(1, 5), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End With Next End With Exit_ErrorHandler: Exit Sub ErrorHandler: MsgBox Err.Number & " Description: " & Err.Description Resume Exit_ErrorHandler End Sub
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
Join the community of 500,000 technology professionals and ask your questions.