0

I am trying to write a program, where I must first apply the filter to Column S with 6 possible filter values (1,2,3,4,5,6). Once the filter is applied, I must copy the last used cell in column T and paste the same in the next available (blank) visible cell in column V with the filter still being on. Once it pastes the values successfully,if I were to apply filter again for the same value, it should repeat the same by finding the next available visible blank cell column V and paste the copied data.

The loop should keep working, For the next instance, based on the condition I might apply the filter for value 2 in the column, I must copy the last used column T data and paste in the next available visible cell in column V with the filter still being on.

I tried many possibilities like End(xlUp) it works only until copying the value from Column T and I am unable to paste it in Column V.I tried using Application.SendKeys method too. It doesn't work all the time.

The following codes that I have pasted down below, I tried copying the value of the last used cell in Column T with End(xlup), which worked all the time, the problem is all about the finding the right next available visible cell in column V to paste the same, sometime it works and sometimes it pastes in the hidden rows

enter image description here

    Sub auto_filter2()

Dim ws As Worksheet
Dim ds As Worksheet
Dim SrcLastRow As Long, DestLastRow As Long

Set ws = Worksheets("PVF")
Set ds = Worksheets("Filtered")

Worksheets("PVF").Range("T4").AutoFilter Field:=19, Criteria1:="2"

MsgBox "Its Working"


Sheets("PVF").Select
SrcLastRow = Cells(Rows.Count, "T").End(xlUp).Row
Range("T" & SrcLastRow).Select
Selection.Copy
MsgBox SrcLastRow & " is the row"


Sheets("PVF").Select
Range("V5").End(xlDown).Select
Application.SendKeys "{DOWN}"
Application.SendKeys ("^v{Enter}")

ws.AutoFilterMode = False

End Sub

Sub auto_filter()

Dim ws As Worksheet
Dim ds As Worksheet
Dim SrcLastRow As Long, DestLastRow As Long

Set ws = Worksheets("PVF")
Set ds = Worksheets("Filtered")

Worksheets("PVF").Range("T4").AutoFilter Field:=19, Criteria1:="4"

MsgBox "Its Working"


Sheets("PVF").Select
SrcLastRow = Cells(Rows.Count, "T").End(xlUp).Row
Range("T" & SrcLastRow).Select
Selection.Copy
MsgBox SrcLastRow & " is the row"


Sheets("Filtered").Select
DestLastRow = Cells(Rows.Count, "C").End(xlUp).Row
Range("C" & DestLastRow).Select
ActiveSheet.Paste
Application.CutCopyMode = False

Dim cel As Range
With Sheets("PVF")
Set cel = .AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible)(1, 1)

If cel = "" Then
.Cells(cel.Row, "V") = Sheets("Filtered").Range("C3")
Else
MsgBox (" Non- empty box")
End If

End With

ws.AutoFilterMode = False

End Sub

I hope to solve this problem soon. Many thanks for your solutions in advance.

Kumar V Reddy
  • 11
  • 1
  • 4
  • See @Dick Kusleika's answer to This question. https://stackoverflow.com/questions/71180/how-can-i-find-last-row-that-contains-data-in-a-specific-column/49971492#49971492 – Roy Jun 24 '21 at 10:36
  • @Roy Unable to run the code, as it is a public function and there's no possibility to enter the desired range in it – Kumar V Reddy Jun 24 '21 at 11:46
  • It might be easier to skip the Filter and instead loop through the rows checking the ColS value, and apply the logic directly instead of having to deal with hidden rows from the Filter. – Tim Williams Jun 24 '21 at 16:29
  • "I must copy the last used cell in column T" - can you clarify / define what you mean by this please? (e.g. does this mean last non-blank cell/last modified cell/last 'selected &/or modified cell" - I feel reading on is futile with this Q in the back of my mind, even if I give credence to one of the above. Niggling feeling to say the least :) – JB-007 Jul 05 '21 at 18:20
  • (also - screenshot/visualise via table or some dummy / sample data would go a long way...) – JB-007 Jul 05 '21 at 18:21

0 Answers0