VBA xltoright but NOT include first coulmns-CodePudding

Worksheets("front").Cells(5, Columns.Count).End(xlToRight).Column 1

already have this code down, works great - problem is .. I dont want it to fill in column A, which happens to be blank so code is working as intended, I need it to skip over and to start taking the next available column AFTER column H... I tried working with some offsetting, but was still not able to get it to do what I need.

Any help greatly appreciated! Seems the resoucres on xlright isnt as common asn xlup :)

Worksheets("front").Cells(5, Columns.Count).End(xlToRight).Column 1

CodePudding user response：

you could be after this;

Option Explicit

Function GetFirstAvailableColumnIndexAfterColumnH(sh As Worksheet) As Long

    With sh               
        GetFirstAvailableColumnIndexAfterColumnH = WorksheetFunction.Max(8, .Cells(5, .Columns.Count).End(xlToLeft).Column)   1
    End With

End Function

to test:

Sub test()
    sh.Cells(5, GetFirstAvailableColumnIndexAfterColumnH(sh)).Value = Date
End Sub

CodePudding user response：

First 'Available' Cell in Row

... i.e. the cell adjacent to the right of the rightmost non-empty cell

Using the End Property

Keep in mind that this will fail if the column of the 'last' cell is hidden (give it a test so it'll sink in). If that's a possibility, rather use the Find method version.

Sub AddTodayEnd()
    Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Front")
    Dim fCell As Range: Set fCell = ws.Range("H5")
    Dim lCell As Range
    Set lCell = ws.Cells(fCell.Row, ws.Columns.Count).End(xlToLeft)
    If lCell.Column < fCell.Column Then Set lCell = fCell
    lCell.Offset(, 1).Value = Date
End Sub

Using the Find Method (Recommended)

Sub AddTodayFind()
    Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Front")
    Dim fCell As Range: Set fCell = ws.Range("H5")
    Dim lCell As Range: Set lCell = fCell.Resize(, ws.Columns.Count _
        - fCell.Column   1).Find("*", , xlFormulas, , , xlPrevious)
    If lCell Is Nothing Then Set lCell = fCell
    lCell.Offset(, 1).Value = Date
End Sub