Problem with using paste while altering autofilter at same time

[Usva]

I have a problem. I have button that copies a row from one sheet and pastes it on another sheets first empty row. It works just fine, untill autofilter is used on page. When some of the parts are filtered the table gets shorter and when that button described above is used it leaves empty space equal to rows not visible due to filtering. Meaning if i have 100 rows and filter them by some part of the table and it leaves 30 rows visible and then use the button to add new pre-made row it wont add on first empty row but 70 rows below instead.

Well I tried turning off the autofilter for duration of paste and then adding the autofilter again, not selecting the used filter mode tho (would be too complicated for me ) But when i use it, it removes the autofilter but wont paste and gives runtime error 1004.

Here is the code:

Private Sub CommandButton3_Click()
Sheets("valmiit").Select
ActiveSheet.Range("A1:J1").Copy
Sheets("työlista").Select
ActiveSheet.Range("A3:J3").AutoFilter
Dim LastRow As Long
r = ActiveSheet.UsedRange.Rows.Count
c = ActiveSheet.UsedRange.Columns.Count
LastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row + 1
Worksheets("työlista").Cells(LastRow, 1).EntireRow.Select
ActiveCell.Offset(0, -ActiveCell.Column + 1).Select
ActiveSheet.Paste
ActiveSheet.Range("A3:J3").AutoFilter
End Sub

Does anyone have solution?

 

[TonyJollans]

Hi Usva,

What I am seeing here strikes me as bizarre and I can't believe the problem hasn't been encountered and resolved before and can only assume I'm being particularly dense today.

Switching off the AutoFilter is setting CutCopyMode to False (I really don't know why it does or it should but it always does it for me - and for you it seems). This means that what you have copied is no longer available for pasting even though it is still on the Clipboard and can be retrieved by other means.

Nothing I have tried so far finds the actual last row properly when there is a filter in place. The only accurate information I can find (and even here it's not easy to extract) is in the built-in [blue]_FilterDatabase[/blue] Named Range.

I'll test some code and post it later (I don't have any more time at the moment) - if no-one else jumps in first and posts what must surely be obvious.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[brucegn]

Not sure if this helps, but I think this is what Tony was referring to

Private Sub CommandButton3_Click()
Sheets("valmiit").Select
ActiveSheet.Range("A1:J1").Copy
Sheets("työlista").Select
ActiveSheet.Range("A3:J3").AutoFilter
Dim LastRow As Long
r = ActiveSheet.UsedRange.Rows.Count
c = ActiveSheet.UsedRange.Columns.Count

'Added
'will find last row of filtered data
With [_FilterDatabase]
LastRow = .Rows(.Rows.Count).Row
End With


Worksheets("työlista").Cells(LastRow, 1).EntireRow.Select
ActiveCell.Offset(0, -ActiveCell.Column + 1).Select
ActiveSheet.Paste
ActiveSheet.Range("A3:J3").AutoFilter
End Sub

 

[TonyJollans]

Hi Usva,

Firstly, thanks to brucegn.

A couple of points:
[ul][li]The _FilterDatabase Name may not exist, so checks must be conditional[/li]
[li]The _FilterDatabase Name will exist if there has been a filter, even if there isn't now. In a sense this doesn't really matter[/li]
[li]The Filter Range may not, and in this case does not, start in Row 1 so using .Rows.Count is not sufficient[/li]
[li]The Filter Range does not necessarily end in the last used row; there could be data in columns outside it which extends lower down the sheet[/li][/ul]

Put it all together and I think this should work ..

[blue]Private Sub CommandButton3_Click()
Sheets("valmiit").Select
ActiveSheet.Range("A1:J1").Copy
Sheets("työlista").Select

Dim LastRow As Long[red], EndFilter As Long

LastRow = Sheets("sheet1").Cells.Find(What:="*", _
                                      SearchDirection:=xlPrevious, _
                                      SearchOrder:=xlByRows).Row
                                      
If Sheets("työlista").FilterMode = False Then
[green]    ' No Filter in effect - last row is good[/green]
Else
[green]    ' There is a filter.
    ' Get larger of LastRow and last row of filter range[/green]
    With Sheets("työlista").Names("_FilterDatabase").RefersToRange
        EndFilter = .Row + .Rows.Count - 1
    End With
    If EndFilter > LastRow Then LastRow = EndFilter
End If
[/red]
Worksheets("työlista").Cells(LastRow, 1).EntireRow.Select
ActiveCell.Offset(0, -ActiveCell.Column + 1).Select
ActiveSheet.Paste
End Sub[/blue]

I have removed your lines dealing with the autofilter, and also setting variables r and c which don't seem to be used.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[VBAjedi]

I dealt with this issue a while back in thread707-683513 . Here's what I ended up with (see the original thread for a more complete explanation):

The end of the FilterDatabase range is not close enough for me, so I found a more accurate way. I realized that the filters don't actually hide blank rows in the FilterDatabase range if they are after the last row with data in it. So comparing the .Find methods last row with the last hidden row on the sheet gives me the true last row with contents in it! Here's what I came up with:

Function LastRow(sh As Worksheet)
' Returns last populated data row if filters active,
' or last row with contents otherwise
Dim a, b, x, z

z = sh.Cells.Find(What:="*", LookIn:=xlFormulas, After:=Range("IV65536"), _
   SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
If sh.FilterMode = True Then
   a = Range(sh.Name & "!_filterdatabase").Cells(1, 1).Row
   b = a + Range(sh.Name & "!_filterdatabase").Rows.Count
   For x = b To a Step -1
      If sh.Range("A" & x).EntireRow.Hidden = True Then
         If x > z Then z = x ' Only rows with contents are hidden
         Exit For
      End If
   Next x
End If
LastRow = z
End Function

So IF filters are active I trigger the loop. Worst case scenario on the loop (60000+ blank rows in the filterdatabase range after the last hidden row) takes about 2 seconds to process on my computer. Not bad. . .

VBAjedi

 

[TonyJollans]

Hi Jedi,

Interesting!

I just tried this out and found that the _FilterDatabase Range seems to be defined using the same criteria as UsedRange - in other words trailing completely blank rows without any cell formatting are not included in the range. I'm sure I haven't tested exhaustively (after all, I didn't consider the situation at all in the first place) but I couldn't find any circumstance where your extra loop made any difference.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[Usva]

Thank you all! I actually figured it out after I read from your posts what the problem was. I changed the order in which i did things in code. Like instead of copying first and changing autofilters off after that I changed filters first and copied then Well this aint perfect solution as this wont return to the original filtering, but this will have to do for now

Oh... here is the code I have now and it works, at least with the testing i did:

Private Sub CommandButton3_Click()
Sheets("työlista").Select
ActiveSheet.Range("A3:J3").AutoFilter
Sheets("valmiit").Select
ActiveSheet.Range("A1:J1").Copy
Sheets("työlista").Select
Dim LastRow As Long
r = ActiveSheet.UsedRange.Rows.Count
c = ActiveSheet.UsedRange.Columns.Count
LastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row + 1
Worksheets("työlista").Cells(LastRow, 1).EntireRow.Select
ActiveCell.Offset(0, -ActiveCell.Column + 1).Select
ActiveSheet.Paste
ActiveSheet.Range("A3:J3").AutoFilter
End Sub