VBA Question for Range on open ended Columns

[arossijr]

I used the Macro Recorder to get the basics, added in for Criteria1 and Criteria2... All worked good till I needed to select certain rows and available cells...
Here is the code I am using with all the "Failures" for selecting the ranges..

Application.ScreenUpdating = False
Dim Startdate, EndDate As Date
Dim LngLastRow As Long



Any and all help welcome!!! Thank you!!!



Startdate = Sheets("Data").Range("B1").Value
EndDate = Sheets("Data").Range("B2").Value


Range("A4").Select
Selection.End(xlDown).Select
LngLastRow = ActiveCell.Row




'ActiveSheet.ListObjects("Data").Range.AutoFilter Field:=1, Operator:= _
'xlFilterValues, Criteria2:=Array(1, "5/31/2018")
'ActivateSheet.ListObjects("Data").Range.AutoFilter Field:=1, Criteria1:=">=" & Startdate, Operator:=xlAnd, Criteria2:="<=" & EndDate
Sheets("Data").ListObjects("Data").Range.AutoFilter Field:=1, Criteria1:=">=" & Startdate, Operator:=xlAnd, Criteria2:="<=" & EndDate

'ActiveWindow.SmallScroll Down:=9
'Range("A4:A108,B4:B108,C4:C108,G4:G108,I4:I108,J4:J108").Select
'Sheets("Data").Range("A4", "B4", "C4", "G4", "I4", "J4").Select
'Sheets("Data").Range(A.Address, B.Address, C.Address, G.Address, I.Address, J.Address).Select
'Sheets("Data").Range("A4,B4,C4,G4,I4,J4").Select
Sheets("Data").Range("A4" & LngLastRow, "B4" & LngLastRow, "C4" & LngLastRow, "G4" & LngLastRow, "I4" & LngLastRow, "J4" & LngLastRow).Select
'Range("J2").Activate
'Sheets("Data").ListObjects("Data").AutoFilter.Range.Copy
Selection.SpecialCells(xlCellTypeVisible).Copy
Sheets("Report").Select
Range("Table1[DATE]").Select
ActiveSheet.Paste
End Sub

 

[arossijr]

My Answer Is This,

Ok Thank You again!, Back to Debugging for me !!
Greatly appreciated!

 

[Norie]

If you have a table, use it.

You can easily refer to the data range in the table like this,

Dim tbl As ListObject
Dim rng As Range

    Set tbl = Sheets("Data").ListObjects("Data")
    Set rng = tbl.DataBodyRange

and the data range of a column in a table like this.

Set rngCol = tbl.ListColumns("Date").DataBodyRange

 

[arossijr]

My Answer Is This, and or maybe others?

This is the line of code or part of that i think is some of the issue.
Range("A4:A110,B4:B110,C4:C110,G4:G110,I4:I110,J4:J110").Select <-- This line establishes a set range... A4,B4,C4,G4,I4,J4 are always the First Row what is the correct way to make this an "Open" range ending on the first blank cell after the filter is applied?

Thanks again!

 

[arossijr]

Ok I see the setup, Do i need to do a Set rngCol for each column that I want to move to the other Table?
I am using tables on both sheets. Do I "Set" both tables? and Set their rngCol as well?

Sorry... Table coding and advanced coding I am still trying to develop and train in..

 

[Norie]

You can refer to multiple columns in a table like this.

    Set rng = Sheets("Data").Range("Data[Date],Data[LOC],Data[Acct],Data[Part '#],Data[Qty Sold],Data[Base]")

PS Note, for some reason I had to add a single quote in front of the # in 'Part #', not sure why - might be worth considering changing the column name.

 

[arossijr]

You can refer to multiple columns in a table like this.

    Set rng = Sheets("Data").Range("Data[Date],Data[LOC],Data[Acct],Data[Part '#],Data[Qty Sold],Data[Base]")

PS Note, for some reason I had to add a single quote in front of the # in 'Part #', not sure why - might be worth considering changing the column name.

Norie, Thank you, I am trying this now... I have seen the single ' in formulas that i use with Tables when I have a Special character like the # sign...
Thanks again.. lets see what happens...

 

[Norie]

I think the # is used to refer to specific parts of a table, for example Data[#Headers] refers to the header row of the table Data.

 

[arossijr]

Norie, My Answer Is This,

Thank you both!!! I now have a working script!! An I learned something in the process!! Thanks again Greatly Appreciated!!

 

[My Aswer Is This]

When I'm doing things I'm not sure about I go one step at a time:
So if you have A Table on a sheet named Data
And you have a Table on this sheet and the Table Name is Data

Try using this script and see if it filter's the data the way you want.
This assumes you have a start Date in range B1 and End date in B2

If this filters properly then we will work on copying the data where you want.

Sub Filter_Me_By_Date()
'Modified  7/25/2018  2:52:10 PM  EDT
Dim StartDate As Date
Dim EndDate As Date
StartDate = Sheets("Data").Range("B1").Value
EndDate = Sheets("Data").Range("B2").Value
With ActiveSheet.ListObjects("Data").DataBodyRange
.AutoFilter Field:=1, Criteria1:=">=" & StartDate, Operator:=xlAnd, Criteria2:="<=" & EndDate
End With
End Sub

 

[My Aswer Is This]

Norie, My Answer Is This,

Thank you both!!! I now have a working script!! An I learned something in the process!! Thanks again Greatly Appreciated!!

Show us your script