Table issue with VBA

Mike2

New Member
Joined
Jan 5, 2019
Messages
42
Hello,
I've created a worksheet that contains real time search box for data in a table.
I am trying to find and filter a specific column that do not have empty cells in it.
Then, copy the entire contents of the filtered rows excluding the header row to another worksheet from the same workbook.
After the copy is done, delete the copied lines from the source sheet.

Due to the fact, the data are in a table, when I use CurrentRegion to select the filtered rows, the codes selected the header row as well.
Is there a way for the codes to copy only the filtered rows excluding the header row to another sheet ?
Also, an error pops up when attempting to delete the copied rows in the source sheet. Why?

If no table were used on worksheet, then the codes work fine. Any help or suggestion to resolve or work around the codes would be greatly appreciated.

Here is what I have coded:
I have two real time Search Boxes and the below vba codes are in the Worksheet

Private Sub TextBox1_Change()
Application.ScreenUpdating = False
ActiveSheet.ListObjects("Table1").Range.AutoFilter field:=3, Criteria1:=[F3] & "*", Operator:=xlFilterValues
Application.ScreenUpdating = True
End Sub

Private Sub TextBox2_Change()
Application.ScreenUpdating = False
ActiveSheet.ListObjects("Table1").Range.AutoFilter field:=4, Criteria1:=[J3] & "*", Operator:=xlFilterValues
Application.ScreenUpdating = True
End Sub


The below vba codes are in the Module 1 assign to a Button for a user to use whenever applicable.

Sub RmCmpTrade1()
Dim LRow As Long, Fltr As Range, Fltr1 As Range

Sheets("Outstanding").Activate

LRow = Cells(Rows.Count, 1).End(xlUp).Row

Set Fltr = Rows(5).Find("TradeStatus")
Fltr.AutoFilter field:=Fltr.Column, Criteria1:="<>"
Fltr.Select
Set Fltr1 = ActiveCell.End(xlDown)
If Fltr1.Value <> "" Then
Fltr1.Select
Sheets("Outstanding").Rows(5).Offset(1).CurrentRegion.Copy
Sheets("Completed").Range("A1").End(xlDown).Offset(1).PasteSpecial xlPasteValues
ActiveCell.CurrentRegion.EntireRow.Delete '//ERROR POPS UP on this line of code //
ActiveSheet.ListObjects(1).AutoFilter.ShowAllData
Else
Range("A5").Select
ActiveSheet.ListObjects(1).AutoFilter.ShowAllData
MsgBox "There is no completed trade to move.", vbInformation, "Message Box"
End If
End Sub
 

Attachments

  • Unwanted header row.JPG
    Unwanted header row.JPG
    65.9 KB · Views: 4
  • Error when attempt to delete rows.JPG
    Error when attempt to delete rows.JPG
    16 KB · Views: 4

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Forum statistics

Threads
1,215,326
Messages
6,124,263
Members
449,149
Latest member
mwdbActuary

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top