Problems cutting visible rows from one sheet and pasting in another

[shirleyj]

Hi Mr Excel,

I am having some trouble putting together the code to cut the visible rows from a filtered list on one sheet, to paste to another worksheet.

These are the steps I am trying to achieve:

1. Filter a range (named “List”) based on criteria set in variable vCriteria
2. Set the visible rows (excl. header row) to a variable named DList
3. Cut (remove) from database tab data set in DList, not leaving any blank rows after the list is unfiltered
4. Go to the archive tab, find the last row in the archive list and paste data contained in DList

Below is the code I have so far. The first step is no problem. The second step, I think works, but the third and the fourth steps, I am really having troubles with. Any help would be much appreciated!

Best regards,

Shirley


Sub Filtertest()

Dim vCriteria As Range
Dim rTable As Range
Dim DList As Range

Set vCriteria = Range("criterion")
Set rTable = Range("List")

'Filter database based on criteria in cell named "criterion"
rTable.AutoFilter field:=2, Criteria1:=vcriteria

‘set visible cells to variable named DList (excluding headers)
With Sheets("Database").AutoFilter.Range

On Error Resume Next

Set DList = .Offset(1, 0).SpecialCells(xlCellTypeVisible)

On Error GoTo 0
End With
If DList Is Nothing Then
MsgBox "No data to copy"

'cut visible cells in variable DList from “Database” sheet and paste on sheet named “Archive”



End if
Sheets(“Database”).select


End Sub

 

[drsarao]

Try on a copy first:

Sub Filtertest()
Dim vCriteria As Range
Dim rTable As Range
Dim DList As Range
Set vCriteria = Range("criterion")
Set rTable = Range("List")
'Filter database based on criteria in cell named "criterion"
rTable.AutoFilter field:=2, Criteria1:=vCriteria
'set visible cells to variable named DList (excluding headers)
Set DList = Sheets("Database").AutoFilter.Range.Offset(1, 0).SpecialCells(xlCellTypeVisible)
'copy visible cells in variable DList from “Database” sheet to sheet named “Archive”
'delete DList cells (cut+paste fails in AutoFilter list)
If DList.Cells(1) = "" Then
    MsgBox "No data to copy"
Else
    lr = Sheets("Archive").Cells(Rows.Count, "A").End(xlUp).Row 'last row
    DList.Copy Sheets("Archive").Cells(lr + 1, "A")
    DList.Delete Shift:=xlUp 'this part takes a long time
End If
Sheets("Database").ShowAllData 'reset Autofilter
End Sub

 

[jim may]

As Drsarao indicated, you can't CUT, you must COPY and Paste, then as a separate step DELETE the Copied (Visible) Rows.

HTH, Jim

 

[shirleyj]

Many thanks for your response! I will give this a go and let you know if it works ...

 

[shirleyj]

Hi drsarao,

Thanks very much for your post. With a few minors adjustments, I managed to make it work – and my spreadsheet now runs perfectly! Your code really helped to point me in the right direction to find the solution I was looking for. As you and Jim May helpfully highlighted, it is not possible to cut rows from a filtered list …. only copy and paste and then delete.

Just to share with you and others how I managed to get this to work. Instead of using the if/else loop, which as you indicated, takes a long time to run, I set the range to a variable, copied/pasted and then deleted. Admittedly, this may not be the most efficient VBA solution, but it does work as I need it to for my particular problem. Feel free by all means to suggest any improvements … I am still learning the VBA ropes!

Best regards,

Shirley


'Set visible cells to a variable named DList (excluding headers)
Set rng = Sheets("Database").AutoFilter.Range.Offset(1, 0)
Set rng1 = rng.SpecialCells(xlCellTypeVisible)
Set Dlist = rng1

Dlist.Copy

'Paste DList to Archive sheet. Find the last row and offset by 1

Sheets("Archive").Range("b65536").End(xlUp).Offset(1, -1).PasteSpecial Paste:=xlFormulas

'delete DList rows
Application.DisplayAlerts = False
Dlist.Delete

 

[drsarao]

You are welcome.

DList.Delete Shift:=xlUp 'if you don't add this Shift Up, Excel will guess what to do. It may not be what you want!

DList.Delete tales a long time only if too many rows need to be deleted. Because Excel deletes each row individually. (Delay is NOT because of if/else)

Just curious. You seem to be pasting Formulas from Database to Archive. Is that such a good idea? Archive should usually have frozen values.
Firstly it should not change due to inadvertant changes in original data. Secondly, lesser the number of formulas, faster is the sheet.
Unless of course, you do want the changes reflected in the Archive.

 

[shirleyj]

In my particular case, there will only ever be one row in DList to be moved to the archive at any one time. Therefore this works, but your solution using the Shift:xlUp is probably better in situations where multiple rows need to be moved at any one time. I did intentionally copy formulas, instead of values, as I may move these rows from the archive back to the database at some stage .... however, it's good to know that pasting as static values will make the sheet run faster.

Once again, thanks for your responses. Really helpful!