CODEWITHSUNDEEP
vbaexcel
QuickSilver
QuickSilver

Reputation: 770

Selecting first empty row with specific columns for each sheet VBA

Is there any way that I can possibly make the function change to a specific column for each sheet in the ActiveWorkbookI tried various versions but can't seem to get it right.

Sub resetFilters()

Dim sht As Worksheet

On Error GoTo ErrorHandler

Application.ScreenUpdating = False

   'On Error Resume Next
        If ActiveSheet.FilterMode Then
    ActiveSheet.ShowAllData

  End If

Range("A3:T3").ClearContents
Application.ScreenUpdating = True

Call GetLastRow


Exit Sub
ErrorHandler:
Debug.Print "Error number: " & Err.Number & " " & Err.Description


End Sub
Private Function SelectFirstEmptyRowInColumnH(ByVal sheet As Worksheet, Optional ByVal fromColumn As Long = 8) As Long

    SelectFirstEmptyRowInColumnH = sheet.Cells(sheet.Rows.Count, fromColumn).End(xlUp).Row

End Function

Private Sub GetLastRow()
Dim selectLastRow As Long

selectLastRow = SelectFirstEmptyRowInColumnH(ActiveSheet, 8)
 Cells(selectLastRow, 8).Offset(1, 0).Select

 End Sub

Upvotes: 0

Views: 862

Answers (4)

Rajesh Sinha
Rajesh Sinha

Reputation: 197

This simple code will help you.

Sub FindFirstEmptyRow()

Cells(Rows.Count, 1).End(xlUp).Offset(1).Select

End Sub ☺ ☺

Upvotes: 0

Variatus
Variatus

Reputation: 14373

A worksheet is an object and can't be passed as an argument ByVal. It must be ByRef, which is the default and can therefore be omitted. Note also that Sheet is a word reserved for VBA's use. In most cases VBA will be able to determine your intention and allow you to use its vocabulary the way you wish, but for you, when faced with the task of trouble shooting, it is a hell of a job to determine in each case whether Sheet means VBA's sheet or your own sheet. Select any word in your code and press F1 to let VBA show you the meaning it attaches to it and how to use it.

Other than that, note that your function returns the last used row. The first empty one is the next one after that. So, I would write that function somewhat like this:-

Private Function FirstEmptyRow(Ws As Worksheet, _
                               Optional ByVal Clm As Long = 1) As Long
    With Ws
        FirstEmptyRow = .Cells(.Rows.Count, Clm).End(xlUp).Row + 1
    End With
End Function

Observe that I changed the default for the optional column to 1. The default should be both the most logical choice and the one most commonly used. In the case of the last row that is the first column, column A.

Here is an alternative based on your comment (which I couldn't fully understand). This code looks for the word "Style" in Rows(3) of the ActiveSheet and returns the next blank row in the column where "Style" was found.

Private Function FirstEmptyRow() As Long
    ' 9 Apr 2017

    Dim Clm As Long

    With ActiveSheet
        On Error GoTo ErrHandler:
        Clm = WorksheetFunction.Match("Style", .Rows(3), 0)
        FirstEmptyRow = .Cells(.Rows.Count, Clm).End(xlUp).Row + 1
    End With
ErrHandler:
    Err.Clear
End Function

If the word "Style" isn't found an error will occur and the execution will jump to the Label ErrHandler: which does nothing. You might want to let it handle the situation in some way. As the function stands the row number it returns will be zero which will cause an error if you try to address that row.

Upvotes: 1

user3598756
user3598756

Reputation: 29421

you could use this function:

Private Function SelectFirstEmptyRowInColumnWithGivenHeader(ByVal sheet As Worksheet, Optional ByVal header As String = "Style") As Long
    Dim col As Variant
    With sheet
        col = Application.Match(header, .Rows(1), 0)
        If Not IsError(col) Then
            .Activate '<--| you must select a sheet to activate a cell of it
            .Cells(.Rows.Count, col).End(xlUp).Offset(1).Select
        End If
    End With
End Function

and exploit it in your main code as follows:

Sub main()
    Dim sht As Worksheet

    Application.ScreenUpdating = False '<--| this to prevent sheet activating slow down the code (and annoy you)
    For Each sht In Worksheets
        SelectFirstEmptyRowInColumnWithGivenHeader sht , "Style" '<--| you can omit the 2nd parameter and it'll be assumed the default column header
    Next
    Application.ScreenUpdating = True '<--| get default behavior back in place
End Sub

Upvotes: 1

J. Garth
J. Garth

Reputation: 803

You can just pass the desired column number to the function. Optional ByVal fromColumn As Long = 8 means that column 8 (column H) is the default column if no column number is passed when the function is called. But passing a column number will override that default.

So in this line, passing the 8 is actually not required, although probably good for clarity, and could be written like so with the same result (returning the last row for column H):

selectLastRow = SelectFirstEmptyRowInColumnH(ActiveSheet)

To change the column number to 2 (column B) for example, you would change the line like so:

selectLastRow = SelectFirstEmptyRowInColumnH(ActiveSheet, 2)

I would also recommend that you genericize the name of the function to SelectFirstEmptyRowInColumn so to avoid confusion.

Upvotes: 0

Related Questions