Next Blank Row

[johnbird1988]

Hello<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I have some code below that relates to a userform and one complete will add it to the next blank row in the "Matrix" sheet.<o></o>
<o></o>
This works absolutely fine however once a filter is applied and on to the range it falls over and finds the last row of the filter and applies the filter to the next cell from there. <o></o>
<o></o>
For example if I had a range of 100 rows and applied a filter to limit it down to 25 it will find the 25th row and add the information to the 26th row and not the 101st row.

Code Below:

[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Private Sub CommandButton1_Click()[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Application.ScreenUpdating = False[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Dim lRow As Long[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Dim ws As Worksheet
Set ws = Worksheets("Matrix")[/FONT][/COLOR]
[COLOR=black][FONT=Verdana][/FONT][/COLOR] 
[COLOR=black][FONT=Verdana]lRow = ws.Cells(Rows.Count, 1) _
  .End(xlUp).Offset(1, 0).Row
  [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]If Trim(Me.TextBox4.Value) = "" Or Trim(Me.TextBox3.Value) = "" Or Trim(Me.TextBox1.Value) = "" Or Me.ComboBox1 = "" Or Me.ComboBox2 = "" Then
  Me.TextBox4.SetFocus
  MsgBox "Please complete all the fields (Employee End Date is Optional)"
  Exit Sub
End If[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]With ws[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]    .Cells(lRow, 1).Value = Me.TextBox4.Value
    .Cells(lRow, 2).Value = Me.TextBox3.Value
    .Cells(lRow, 3).Value = Me.ComboBox1.Value
    .Cells(lRow, 4).Value = Me.ComboBox2.Value
    .Cells(lRow, 5).Value = Me.TextBox1.Value
    .Cells(lRow, 6).Value = Me.TextBox2.Value[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]End With[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Unload New_Employee[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Application.ScreenUpdating = True[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]End Sub[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]

<o></o>
PLEASE HELP driving me nuts<o></o>
<o></o>
Thanks <o></o>
<o></o>
John<o></o>

 

[ChrisOswald]

hi,

try inserting this snippet in your code:

Dim i As Long
[COLOR=black][FONT=Verdana]Dim lRow As Long[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Dim ws As Worksheet[/FONT][/COLOR]
 
[COLOR=black][FONT=Verdana]Application.ScreenUpdating = False[/FONT][/COLOR]
[FONT=Verdana][COLOR=black]Set ws = Worksheets("Matrix")[/COLOR][/FONT]
 
[COLOR=black][FONT=Verdana]lRow = ws.Cells(Rows.Count, 1) _[/FONT][/COLOR]
[FONT=Verdana][COLOR=black].End(xlUp).Offset(1, 0).Row[/COLOR][/FONT]
 
 
If ws.AutoFilterMode Then
    i = lrow + 1
    Do
        If Not ws.Rows("i" & ":" & "i").hidden Then
            lrow = i - 1
            Exit Do
        End If
    Loop
End If