autofilter

[alex_peng]

Hello, I am new to this site and I have a question regarding autofilter. Any help would be appreciated.

I have a table which I have imported from a csv file on a website. "http://reports.ieso.ca/public/PriceHOEPPredispOR/PUB_PriceHOEPPredispOR_2020.csv"

I use table to text and It comes in fine as a table. DATE(YYYY-MM-DD) I
(Date'Hour'Price) Header -
I deleted some rows and columns that were not needed and redefined the date cell.
.Columns("A:B").ColumnWidth = 12
.Range("d:i").EntireColumn.Delete
.Range("1:3").EntireRow.Delete
.Range("A500000").NumberFormat = "yyy-mm-dd"

I am only looking for data for the current month (Day 1-31)

am using the following criteria

With ActiveSheet
.Range("a1").AutoFilter Field:=1, _
Criteria1:=XlDynamicFilterCriteria.xlFilterThisMonth, _
Operator:=xlFilterDynamic
End With

When I apply this filter it appears to work however it appears that all the row heights have been set to 0. Due to this fact, I cant use the "xltypevisisble" to copy because all the rows are equal to 0. If I try to change row height manually in the worksheet or in vba it does nothing. However, I can stretch each row one by one to reveal the filtered data.
I went into excel and selected the autofilter and it brings up option boxes for each day. If I select each box for the month it works fine.
Hopefully someone can decipher my gibberish. Any help would be appreciated.
Thank you.
Alex

 

[Logit]

.
Hmm .. not having that issue here.` I opened the CSV file, included the macro, then saved the workbook as MacroEnabled.

Download workbook : PUB_PriceHOEPPredispOR_2020 - 2.xlsm

 

[alex_peng]

Thanks for the response Logit,
Perhaps it has something to do with the way I imported the csv. file from VBA

str = "http://reports.ieso.ca/public/PriceHOEPPredispOR/PUB_PriceHOEPPredispOR_2020.csv"
QueryQuote:
With Sheets("HOEP_Historical").QueryTables.Add(Connection:="URL;" & str, Destination:=Sheets("HOEP_Historical").Range("a1"))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
.Delete
End With

Sheets("HOEP_Historical").Range("a1").CurrentRegion.TextToColumns Destination:=Sheets("HOEP_Historical").Range("a1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, other:=True, OtherChar:=",", FieldInfo:=Array(1, 2)

Sheets("HOEP_Historical").Columns("A:B").ColumnWidth = 12
Sheets("HOEP_Historical").Range("d:i").EntireColumn.Delete
Sheets("HOEP_Historical").Range("1:3").EntireRow.Delete
Sheets("HOEP_Historical").Range("A500000").NumberFormat = "yyy-mm-dd"
Set ws = ActiveWorkbook.Worksheets("HOEP_Historical")


Let me know if you see something odd. Thanks again.

 

[alex_peng]

this is the end result of the filter. You can see the rows 1 to 4223 are not visible. I can grab the bar and drag down to reveal the filtered rows.

 

[alex_peng]

you can see that I draged down to reveal the two rows. This has been driving me crazy. Thanks again.

 

[Logit]

.

Sub ImprtFiltr()
Dim Str As String

Str = "http://reports.ieso.ca/public/PriceHOEPPredispOR/PUB_PriceHOEPPredispOR_2020.csv"

Application.ScreenUpdating = False

QueryQuote:
With Sheets("HOEP_Historical").QueryTables.Add(Connection:="URL;" & Str, Destination:=Sheets("HOEP_Historical").Range("a1"))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
.Delete
End With

Sheets("HOEP_Historical").Range("a1").CurrentRegion.TextToColumns Destination:=Sheets("HOEP_Historical").Range("a1"), DataType:=xlDelimited, _
TextQualifier:=xlDelimited, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, other:=True, OtherChar:=",", FieldInfo:=Array(1, 2)

Columns("A:A").Replace What:=" *", Replacement:="", LookAt:=xlPart
    Columns("A:A").TextToColumns Destination:=Range("A1"), _
        DataType:=xlDelimited, FieldInfo:=Array(1, xlYMDFormat)
    Columns("A:A").NumberFormat = "mm-dd-yyyy"

Sheets("HOEP_Historical").Columns("A:B").ColumnWidth = 12
Sheets("HOEP_Historical").Range("d:i").EntireColumn.Delete
Sheets("HOEP_Historical").Range("1:3").EntireRow.Delete

Sheet1.Range("A1:C1000").AutoFilter field:=1, Criteria1:=xlFilterThisMonth, Operator:=xlFilterDynamic

Sheets("HOEP_Historical").Range("A2:A500000").NumberFormat = "yyyy-mm-dd"

Application.ScreenUpdating = True

End Sub

 

[alex_peng]

Hi Logit,

Thanks for the code changes. Looks like the data is coming in perfect into HOEP historical, but it hangs up in a weird spot. I have this subroutine being called from a button rather than a macro. Not sure if that makes a difference on execution .

 

[Logit]

.
See if this change makes the difference.

Sheets("HOEP_Historical").Range("a1").CurrentRegion.TextToColumns Destination:=Sheets("HOEP_Historical").Range("a1"), DataType:=xlDelimited, _
TextQualifier:=xlDelimited, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, other:=True, OtherChar:=",", FieldInfo:=1

Edit the last section as shown ... the last attribute : FieldInfo:=1

 

[alex_peng]

Looks like it solved that problem. However, when it went through the autofilter it does that same thing by scrunching all the rows together. I dont think I have any hair left. lol
This help is invaluable and is much appreciated. Let me know if you see anything weird in the autofilter code.

 

[Logit]

When the autofilter functions, does it display all dates in June ?