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

Currently, the VBA code below starts from A2. I would like to change the VBA code below…

  1. User

    Currently, the VBA code below starts from A2. I would like to change the VBA code below to start from the next available cell column A

    Sub linkedFolder() Dim folderPath As String Dim files As Object Dim file As Object Dim rowNum As Long folderPath = "G:\Shared drives\Swan School Site Premises\PREMISES MANAGEMENT\SERVICE PROVIDERS\zzzz ServProvDocs\Premises Staff Schedules" Set files = CreateObject("Scripting.FileSystemObject").GetFolder(folderPath).files rowNum = 2 For Each file In files If Not file.Attributes = 16 Then Sheets("Linked Docs").Cells(rowNum, 1).Value = file.Name Sheets("Linked Docs").Cells(rowNum, 2).Value = Right(file.Name, Len(file.Name) - InStrRev(file.Name, ".")) rowNum = rowNum + 1 End If Next file Call Module31.SearchVBALinkedDocs End Sub

  2. Assistant

    To start from the next available cell in column A, you can find the last used cell in column A and set the rowNum variable to the row number of the next available cell. Here's the modified code:

    Sub linkedFolder() Dim folderPath As String Dim files As Object Dim file As Object Dim rowNum As Long folderPath = "G:\Shared drives\Swan School Site Premises\PREMISES MANAGEMENT\SERVICE PROVIDERS\zzzz ServProvDocs\Premises Staff Schedules" Set files = CreateObject("Scripting.FileSystemObject").GetFolder(folderPath).Files

    ' Find the last used cell in column A
    rowNum = Sheets("Linked Docs").Cells(Rows.Count, 1).End(xlUp).Row + 1
    
    For Each file In files
        If Not file.Attributes = 16 Then
            Sheets("Linked Docs").Cells(rowNum, 1).Value = file.Name
            Sheets("Linked Docs").Cells(rowNum, 2).Value = Right(file.Name, Len(file.Name) - InStrRev(file.Name, "."))
            rowNum = rowNum + 1
        End If
    Next file
    
    Call Module31.SearchVBALinkedDocs
    

    End Sub

    This code uses the Cells(Rows.Count, 1).End(xlUp).Row to find the last used cell in column A and then adds 1 to get the row number of the next available cell.