-1

I need to manipulate an Excel worksheet in code using MS Access and need to find the last used column in my opened Excel file. I would also need to find the last used row in a column.

braX
  • 11,506
  • 5
  • 20
  • 33
Vlado
  • 839
  • 6
  • 16
  • 1
    What have you tried so far? – Marcucciboy2 Feb 28 '20 at 20:12
  • Is there any code that you have attempted or looked up? If so, can you post here? If not, try searching for VBA code to open Excel and locate the last column in a Worksheet and see if that works for you. – Eliot K Feb 28 '20 at 20:13
  • @ Marcucciboy2: Thanks for your answer – Vlado Feb 28 '20 at 20:14
  • Your question is not really suitable for SO. You'll find plenty of hits when googling, like this one [VBA Tutorial: Find the Last Row, Column, or Cell on a Sheet](https://www.excelcampus.com/vba/find-last-row-column-cell/) – Rene Feb 28 '20 at 20:14
  • 1
    https://www.rondebruin.nl/win/s9/win005.htm – Marcucciboy2 Mar 04 '20 at 16:48

1 Answers1

1

Here are functions I am using:

Public Function FindLastColumnInWSheet(ws As Worksheet) As Long
    Dim LastCol As Long
    Dim rng As Range

    Set rng = ws.Cells.Find(What:="*", _
                After:=ws.Range("A1"), _
                Lookat:=xlPart, _
                LookIn:=xlFormulas, _
                SearchOrder:=xlByColumns, _
                SearchDirection:=xlPrevious, _
                MatchCase:=False)

    If rng Is Nothing Then
        LastCol = 1
    Else
        LastCol = rng.column
    End If
    FindLastColumnInWSheet = LastCol    
End Function


Public Function FindLastRowInColumn(wsheet As Worksheet, columnName As String) As Integer
     With wsheet
        FindLastRowInColumn = .Range(columnName & .rows.Count).End(xlUp).row
     End With
End Function
cecopes
  • 85
  • 6
  • Do you remember where you found that code from? I know at this point it's rather generic but I have a [similar chunk of code](https://stackoverflow.com/a/51638577/2727437) that was probably inspired by the same snippet as yours and I want to credit the original (or the most original version that I can find) – Marcucciboy2 Mar 04 '20 at 17:00
  • We are using this code in our enterprise application. I do not know who has written it. – cecopes May 12 '20 at 20:34