refer to column cell range - different for every sheet-CodePudding

I have an excel document with multiple tabs, in each of which I need to fill a blank column so that it's in line with the other columns. The columns look something like this:

Col A      Col B        Col C
Data       NoData       Data
Data       NoData       Data
Data       NoData       Data
Data       NoData       Data
Data       NoData       Data
Data       NoData       Data
Data       NoData       Data
Data       NoData       Data
Data       NoData       Data
Data       NoData       Data
Data       NoData       Data
Data       NoData       Data
Data       NoData       Data
Data       NoData       Data

The issue is that each tab has a different number of rows filled, so I can't set the range for "Col B" to one set value. It needs to be calculated separately for each tab.

To find the range for "Col B" I am trying the following right now:

'this doesn't work  
Dim num_rows As Integer
Dim rng As Range

    ActiveSheet.Range("A2").Select 'neighbouring column, is filled
    num_rows = Range(Selection, Selection.End(xlDown)).Rows.Count '# rows in filled column
    rng = ("B2" : Range("B2").Offset(num_rows,0)) 'empty column
        
        For Each c In rng
        
            c.Activate
            ActiveCell.Value = "some value"
    
        Next c

However, since I just started learning VBA, I am not sure how to properly express this range statement's syntax:

'this doesn't work     
rng = ("F2" : Range("F2").Offset(num_rows,0))

I am trying to make it refer to (the first empty cell at the top of the column : the last cell that is in line with the last filled row of the neighboring columns), if that makes sense.

Is there a way to set the rng variable this way?

EDIT: reading up on how to do this right now, and basically, I'm looking for something like this?

Sub test()
'this also doesn't work 

Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range

rng1 = Cells(2, 6) 'column 6 is empty
rng2 = Cells(Rows.Count, 5).End(xlUp).Offset(0, 1) 'column 5 has values; counting to end of values here

Range(rng1, rng2).Select


End Sub

However, still not sure about proper syntax here. The rng1 and rng2 variables do not seem to be working.

Any advice would be appreciated!

CodePudding user response：

Next Available Column

This will work if your table data (has one row of headers) starts in cell A1 of a worksheet (Sheet1) in the workbook containing this code (ThisWorkBook) i.e. it is contiguous i.e. it has no empty rows or columns.

Option Explicit

Sub NextColumn()
    
    Dim wb As Workbook: Set wb = ThisWorkbook 'workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Worksheets("Sheet1")
    Dim rg As Range: Set rg = ws.Range("A1").CurrentRegion
    
    ' New (Next Available) Column Range
    Dim ncrg As Range: Set ncrg = rg.EntireRow.Columns(rg.Columns.Count   1)
    ncrg.Cells(1).Value = "New Column"
    
    ' New Column Data Range (no headers)
    Dim ncdrg As Range: Set ncdrg = ncrg.Resize(ncrg.Rows.Count - 1).Offset(1)
    
    Dim nCell As Range

    For Each nCell In ncdrg.Cells
        nCell.Value = "some value"
    Next nCell
    
    ncdrg.EntireColumn.AutoFit
    
End Sub

EDIT

You have edited the question quite a lot of times so here's something that refers to the current state of it.

Sub FillColumn()
'fills blank column in each tab to last row of surrounding columns
'this one works!!

    Dim ws As Worksheet
    Dim rng As Range
    Dim lRow As Long
    Dim Col As Long
    
    For Each ws In ThisWorkbook.Worksheets
        lRow = ws.Cells(ws.Rows.Count, "E").End(xlUp).Row
        Col = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column   1
        Set rng = ws.Range(ws.Cells(2, Col), ws.Cells(lRow, Col))
        ' The loop is not needed here,...
'        Dim cell As Range
'        For Each cell In rng.Cells
'            cell.Value = "some value"
'        Next cell
        ' ... you can simply do:
        rng.Value = "some value"
    Next ws

End Sub

CodePudding user response：

All your Range references are implicit ActiveSheet. Use a Worksheet variable, and reference that.

Something like

Sub LoopTheSheets
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim RngToFill As Range
    Dim NewData As Variant

    NewData = "some value"
    Set wb = ThisWorkbook ' or ActiveWorkbook or Workbooks("NameOfYourBook")

    ' if you want to process all sheets.
    For Each ws In wb.Worksheets
        Set RngToFill = GetRange(ws, 2)
        If Not RngToFill Is Nothing Then
            RngToFill.Value2 = NewData
        End If
    Next
End Sub

Function GetRange(ws As Worksheet,  colNum as Long) As Range
    Dim DataCol As Long
    Dim LR As Long

    If colNum = 1 Then
        DataCol = 2
    Else
        DataCol = colNum - 1
    End If

    LR = ws.Cells(ws.Rows.Count, DataCol).End(xlUp).Row
    If LR = 1 Then
        ' no data
        Set GetRange = Nothing
    Else
        Set GetRange = ws.Range(ws.Cells(2, colNum), ws.Cells(LR, colNum))
    End If
End Function

CodePudding user response：

After playing around more with the code, got it to a point where it works. This is not the best coding practice, so for better examples please see the other answers here! Will have to get back to this one and re-write in the future. For now, this seems to be functional though:

Sub fill_column()
'fills blank column in each tab to last row of surrounding columns
'this one works!!


Dim how_many_sheets As Integer

how_many_sheets = ThisWorkbook.Sheets.Count

For i = 1 To how_many_sheets
    
    Dim rng As Range
    
    Sheets(i).Activate
    Set rng = Range(Cells(2, 6), Cells(Rows.Count, 5).End(xlUp).Offset(0, 1)) 'filling the 6th column based on the 5th column's row count
    
    For Each c In rng
    
        c.Activate
        ActiveCell.Value = "some value"

    Next c

Next i

End Sub