CODEWITHSUNDEEP
vbaexcel
DaBeau96
DaBeau96

Reputation: 548

How to select the first and last cells of a filtered table?

The code below is to go to a table and filter by which animal came in. The animal options are dog, cat, and hamster. It then pulls the date and time and pastes it on a different sheet.

My question has to do with that a random number of dogs could show up any day let's say 37 show up.

I can get that data to pull correctly, but then when I go to pull the cats it starts on row 38 and my code keeps pulling from row 2.

How can I get my code to start on the right row no matter what animal is chosen and select the first and last cell of the filter table?

Sub VetDate()
str = ""
str2 = ""
 With Sheet1
  .AutoFilterMode = False
   With .Range("A1:N1")
    .AutoFilter
    .AutoFilter Field:=9, Criteria1:="dog"
    str = .Range("A2").Value 'How to fix this part
    str2 = .Range("A1").End(xlDown).Value 'How to fix this part
    End With
  End With
  With Sheet2
   .Range("D36:D37").ClearContents
   .Range("D36").Value = Format(str, "mmm-dd-yy hh:mm am/pm")
   .Range("D37").Value = Format(str2, "mmm-dd-yy hh:mm am/pm")
   .Range("D36:D37").HorizontalAlignment = xlCenter
 End With
End Sub

Upvotes: 1

Views: 65

Answers (1)

adhy wijaya
adhy wijaya

Reputation: 509

Please try this:

Sub VetDate()

Dim StrRow As Long
Dim Str2Row As Long

str = ""
str2 = ""
 With Sheet1
  .AutoFilterMode = False
   With .Range("A1:N1")
    .AutoFilter
    .AutoFilter Field:=9, Criteria1:="dog"
    StrRow = Activesheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 2).Row
    Str2Row = Range("A" & Rows.Count).End(xlUp).Row
    str = .Range("A" & Str2Row).Value 'How to fix this part
    str2 = .Range("A" & StrRow).Value 'How to fix this part
    End With
  End With
  With Sheet2
   .Range("D36:D37").ClearContents
   .Range("D36").Value = Format(str, "mmm-dd-yy hh:mm am/pm")
   .Range("D37").Value = Format(str2, "mmm-dd-yy hh:mm am/pm")
   .Range("D36:D37").HorizontalAlignment = xlCenter
 End With
End Sub

Hope this help

Upvotes: 2

Related Questions