Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0125· OpenAIAi2 WildChat-4.8M (AI2)

Can you please write me a VBA code that will do the following: On my current sheet, In…

  1. User

    Can you please write me a VBA code that will do the following:

    On my current sheet, In the row range A2:F2 select the cell with a value on the same row just before the next empty cell in the range.

  2. Assistant

    Sure! Here is a VBA code that will accomplish the task you described:

    Sub SelectLastCellWithValue()
        Dim ws As Worksheet
        Dim lastCell As Range
        Dim cell As Range
        
        ' Set the worksheet to the active sheet
        Set ws = ActiveSheet
        
        ' Loop through each cell in the range A2:F2
        For Each cell In ws.Range("A2:F2")
            ' Check if the cell is empty
            If cell.Value <> "" Then
                ' Set the lastCell to the current cell
                Set lastCell = cell
            Else
                ' Exit the loop when an empty cell is found
                Exit For
            End If
        Next cell
        
        ' Select the last cell with a value in the range A2:F2
        If Not lastCell Is Nothing Then
            lastCell.Select
        End If
    End Sub
    

    You can run this code in the VBA editor by pressing Alt + F11, inserting a new module, and pasting the code in the module. Then, you can run the code by pressing F5. This will select the cell with a value on the same row just before the next empty cell in the range A2:F2 on your active sheet.