0

Hi I am having issues getting to the last column used in my sheet.

I do know that as it stands my last column is 43 and i want it to enter a value at column 44, so then when i search again it will go to column 44 as last column used and then enter a value at 45 and so on and so on. I am using a ref num to find the correct row.

The code I am using is as follows to try get the last column 

'find last empty Col in database
LastCol = sou.Cells(2, Columns.count).End(xlToLeft).Column + 1
MsgBox LastCol

'For Loop to cycle through the columns
For x = 2 To LastCol

    'If the row value at column 1 = the ref number then continue
    If sou.Cells(x, 1).Value = refNum Then
        sou.Cells(x, LastCol).Value = Me.commentBox.Text
        Exit For
    End If

Next

The issue is that for some reason it only goes to column 22. I have tried to use the other ways to get the last column but they have just given me the last column in the whole entire sheet which again is not what I want.

If someone can lend me a hand as I'm a newbie to this it would be greatly appreciated!

mono92
  • 17
  • 2
  • 7
  • Is that because Column 22 is empty for that row. have a look at : https://www.rondebruin.nl/win/s9/win005.htm – SCramphorn May 11 '17 at 15:57
  • 3
    You are checking the last column in row 2 so I guess that is column 22, but in another row it might be 43. You need to use Find I think. – SJR May 11 '17 at 15:58
  • The annoying thing is there are some blanks as the sheet is being populated from a different excel form! not all fields are always going to be inputted. Any way to cycle through it to find the last value in the whole row as that will always be filled in ? Also thanks for the help thus far – mono92 May 11 '17 at 16:04
  • record a macro where you press `ctrl-end`. takes to you the last row and last column. – xQbert May 11 '17 at 16:04
  • [this question/answer](http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba) illustrates how to find the last row or column in a given range or worksheet. – David Zemens May 11 '17 at 16:07
  • Not sure what you're asking there, but the link provided by @DavidZemens covers all the angles. – SJR May 11 '17 at 16:23

1 Answers1

1

To find the last row used, have you tried

lastRow = ActiveSheet.UsedRange.Rows.Count

If that didn't work, please elaborate what went wrong and I can submit more code.

  • 1
    Before using that code, you would need to ensure that row 1 of the sheet was part of `UsedRange`. E.g. If the first used row was 10, and the last used row was 12, there are only 3 rows in `UsedRange`. Alternatively, you need to say something like `lastRow = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row - 1`. – YowE3K May 11 '17 at 21:37