Won't find the data on the first line or the last line with this code

[Nlhicks]

Is there some kind of trick I don't know about that will filter and show the data in the top row (row 2) and the last row?


FindRightRow()

Dim XfmrUpdate As Worksheet, Sheet3 As Worksheet
Dim x As Long
Dim y As Range, z As Range
Dim c As Range
'Dim resp As VbMsgBoxResult
'Dim ans As Variant
'Dim rnData As Range,
Dim Ws As Range

Set XfmrUpdate = Worksheets("Xfmr Update")
Set Sheet3 = Worksheets("Sheet3")
Set Ws = Sheet3.UsedRange

With Sheet3.Range("A1")

If XfmrUpdate.Range("C3").Value <> "" Then .AutoFilter field:=1, Criteria1:="*" & XfmrUpdate.Range("C3") & "*"
If XfmrUpdate.Range("C4").Value <> "" Then .AutoFilter field:=2, Criteria1:="*" & XfmrUpdate.Range("C4") & "*"
Rowz = Application.WorksheetFunction.Subtotal(4, Range("A2:N" & Rows(Rows.Count).End(xlUp).Row))

If Rowz > 0 Then
Worksheets("Xfmr Update").Range("C8").Value = Worksheets("Sheet3").Range("C2:C685").SpecialCells(xlCellTypeVisible)
Worksheets("Xfmr Update").Range("C9").Value = Worksheets("Sheet3").Range("D2:D685").SpecialCells(xlCellTypeVisible)
Worksheets("Xfmr Update").Range("C10").Value = Worksheets("Sheet3").Range("E2:E685").SpecialCells(xlCellTypeVisible)
Worksheets("Xfmr Update").Range("C11").Value = Worksheets("Sheet3").Range("F2:F685").SpecialCells(xlCellTypeVisible)
Worksheets("Xfmr Update").Range("C12").Value = Worksheets("Sheet3").Range("G2:G685").SpecialCells(xlCellTypeVisible)
Worksheets("Xfmr Update").Range("C13").Value = Worksheets("Sheet3").Range("H2:H685").SpecialCells(xlCellTypeVisible)
Worksheets("Xfmr Update").Range("C14").Value = Worksheets("Sheet3").Range("I2:I685").SpecialCells(xlCellTypeVisible)
Worksheets("Xfmr Update").Range("C15").Value = Worksheets("Sheet3").Range("J2:J685").SpecialCells(xlCellTypeVisible)
Worksheets("Xfmr Update").Range("C16").Value = Worksheets("Sheet3").Range("K2:K685").SpecialCells(xlCellTypeVisible)
Worksheets("Xfmr Update").Range("C17").Value = Worksheets("Sheet3").Range("L2:L685").SpecialCells(xlCellTypeVisible)
Worksheets("Xfmr Update").Range("C18").Value = Worksheets("Sheet3").Range("M2:M685").SpecialCells(xlCellTypeVisible)
Worksheets("Xfmr Update").Range("C19").Value = Worksheets("Sheet3").Range("N2:N685").SpecialCells(xlCellTypeVisible)
Worksheets("Xfmr Update").Range("C20").Value = Worksheets("Sheet3").Range("O2:O685").SpecialCells(xlCellTypeVisible)
Worksheets("Xfmr Update").Range("C21").Value = Worksheets("Sheet3").Range("P2:P685").SpecialCells(xlCellTypeVisible)
Worksheets("Xfmr Update").Range("C22").Value = Worksheets("Sheet3").Range("Q2:Q685").SpecialCells(xlCellTypeVisible)
Worksheets("Xfmr Update").Range("C23").Value = Worksheets("Sheet3").Range("R2:R685").SpecialCells(xlCellTypeVisible)
'End If

End With


End Sub

 

[johnnyL]

Your code doesn't seem to match up with your question.

If all you want to do is show the first row (Row 2) and the last row of data on a sheet by filtering, try the following:

Sub TestFirstLastRow()
'
    Dim LastColumnNumberInSheet As Long
    Dim LastRowInSheet          As Long
    Dim LastColumnLetterInSheet As String
'
    LastColumnNumberInSheet = Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column                               ' Get the number of the last column used in the sheet
    LastRowInSheet = Cells.Find("*", , xlFormulas, , xlByRows, xlPrevious).Row                                              ' Get the last row number used in the sheet
'
    Cells(1, LastColumnNumberInSheet + 1) = "Temp Header"                                                                   ' Assign a Header to the next blank column in the sheet
    Range(Cells(2, LastColumnNumberInSheet + 1), Cells(LastRowInSheet, LastColumnNumberInSheet + 1)).Formula = _
            "=IF(ROW()=2,""1st Row"",IF(B3="""",""Last Row"",""""))"                                                        ' Assign formulas to the added filter column
'
    LastColumnLetterInSheet = Split(Cells(1, LastColumnNumberInSheet + 1).Address, "$")(1)                                  ' Get the Letter of the Filter column that was added
'
    Columns(LastColumnLetterInSheet & ":" & LastColumnLetterInSheet).AutoFilter                                             ' Assign Filter
    Range(LastColumnLetterInSheet & "1:" & LastColumnLetterInSheet & LastRowInSheet).AutoFilter Field:=1, Criteria1:="<>"   ' Filter the data for the first and last row
End Sub

If there is more to what you are wanting to do, please provide more specifics.

 

[Nlhicks]

Hi Johnny ,

What I am trying to say is, when I do my filtering: If it is the very first row (Row 2) for instance then, it will pull up the data but then it hides for some reason. The last row I know that it has to do with the UsedRange command. I did see that there was a way around it but I am not sure how to apply the way around to what I am doing. The way around was its own code that was pretty involved and I am not sure how to incorporate that with all that is going on in my own code and make it work. I am going to try and alter my formulas to include some of what you have in that code above and see if I can make it happen. Thank you. If you have any other suggestions please let me know what they may be.