CODEWITHSUNDEEP
vbaexcelexcel-2010
Manivannan KG
Manivannan KG

Reputation: 371

Dynamically set Ranges to the columns in VBA

I am importing some date to worksheet which needs to be ranged for validation and reference in other worksheets.

Say I have 4 columns in worksheet(WS1) but the row count is dynamic on every import. How can i range the columns(A:D)?

Please help.

Regards, Mani

Upvotes: 0

Views: 2513

Answers (3)

Gilligan
Gilligan

Reputation: 176

Seems like you could just use something like this in the sheet module: 

    Private Sub Worksheet_Change(ByVal target As Range)
        Dim i As Long
        Dim NamesOfNames(1 To 4) As String

            NamesOfNames(1) = "NameOfColumn1"
            NamesOfNames(2) = "NameOfColumn2"   
            NamesOfNames(3) = "NameOfColumn3"
            NamesOfNames(4) = "NameOfColumn4" 

        For i = 1 To 4
            ThisWorkbook.Names.Add Name:=NamesOfNames(i), _ 
                RefersTo:=Range(Cells(1, i), Cells(Cells(Rows.Count, i).End(xlUp).Row, i))
        Next i
    End Sub

Upvotes: 0

Manivannan KG
Manivannan KG

Reputation: 371

Thanks anyways. But what i wanted was just to Name ranges the columns in worksheet.

I have already accomplished the copy and paste (Loading data b/w worksheets).

This is what i wanted.

vRowCount = DestWorkSheet.Cells(1, 1).SpecialCells(xlCellTypeLastCell).Row
vColCount = DestWorkSheet.Cells(1, 1).SpecialCells(xlCellTypeLastCell).Column

DestWorkSheet.usedRange.Columns.AutoFit
AddNamedRange Dest_RATES, DATA_Dest_RATES

Where AddNamedRange is a function,

    Public Sub AddNamedRange(ByVal sheetCodeName As String, ByVal namedRange As String)
    Dim rngToBeNamed As Range
    Dim ws As Worksheet

   On Error GoTo AddNamedRange_Error

   Set rngToBeNamed = GetUsedRange(sheetCodeName)
   Set ws = rngToBeNamed.Worksheet
   ws.Names.Add name:=namedRange, RefersTo:=ws.Range(rngToBeNamed.Address)

   On Error GoTo 0
   Exit Sub


  AddNamedRange_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure AddNamedRange of Module     UtilitiesRange"

    End Sub

Regards,

Mani

Upvotes: 0

peege
peege

Reputation: 2477

Use a lastRow variable to determine the last row. I included a few examples of this. Also on this example is a lastCol variable.. You can use this if the number of Columns is dynamic as well.

Private Sub lastRow()

Dim lastRow As Long
Dim lastCol As Long
Dim sheet As String

    sheet = "WS1"

    lastRow = Sheets(sheet).Range("A" & Rows.Count).End(xlUp).row   'Using Range()
    lastRow = Sheets(sheet).Cells(Rows.Count, "A").End(xlUp).row    'Using Cells()

    lastCol = Sheets(sheet).Cells(2, Columns.Count).End(xlToLeft).Column
End Sub

You can loop through your sheet easily enough using variables also. Using Cells(row,col) instead of Range(A1). you can use numbers or a letter in quotes for the column as shown in the example.

This example looks at WS1 and matches someValue. If the value in Column A of WS1 = somevalue, the record is copied to a "Master" Sheet.

Sub LoopExample()
Dim mRow As Long  'used for a master row
    For lRow = 2 To lastRow
        If Sheets(sheet).Cells(lRow, 1) = someValue Then
            'perform something here like this.  Copy columns A:D to the Master Sheet if match
            For lCol = 1 To 4   'or you could go 1 to lastCol if you needed it dynamic
                Sheets("MASTER").Cells(mRow, lCol) = Sheets(sheet).Cells(lRow, lCol) 'mRow as Row on Master
            Next lCol
            mRow = mRow + 1   'Increment the Master Row
        End If
    Next lRow
End Sub

Upvotes: 1

Related Questions