Currently, the VBA code below starts from A2. I would like to change the VBA code below…
- 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
- 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.SearchVBALinkedDocsEnd Sub
This code uses the
Cells(Rows.Count, 1).End(xlUp).Rowto find the last used cell in column A and then adds 1 to get the row number of the next available cell.