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

 

[alex_peng]

Yeah it looks like to autfilter date selection is correct. The cells are not hidden, but they show up as in the image above and highligted. I call it scrunched for lack of better technical term. I tried the unhide command in excel as well as row height with no luck. what you see in the pic is me taking handle bar at row i and stretching down to reveal the rows which are in reverse order starting at 4321, meaning as I drag the selection to display rows, it shows the last cell first,and up. Not sure if there is a way in vb to determine what state the rows are in?? I am using vba 7.1

 

[Logit]

There is something else going on there. The code functions as desired here :

 

[alex_peng]

Yeah its really weird. Could you send me your file if possible? I am going to try and run from a macro. thanks.

 

[Logit]

Import CSV.xlsm

 

[alex_peng]

awesome, works great, when I run it again on the same sheet looks like it hangs up. If I clear the area it works fine.
I really just want to copy the filtered data to a new sheet called "HOEP", we can delete the data at that point cut/paste. The spreadsheet is updated daily and I only need to run once for that particular month on the 1st.

 

[Logit]

" awesome, works great, when I run it again on the same sheet looks like it hangs up. If I clear the area it works fine. "

Again, it runs fine no matter how many times I run it here. If it hangs the second go round on your computer ... there is something else going on with your system.

 

[alex_peng]

It might be my office.. I opened your file again and ran the macro and it didnt work, did the same thing mine did originally...again no hair left at all. Do you think its worth upgrading to 2019

 

[Logit]

.
I upgraded to 2019 then dumped it. I'm running 2007 and find it to be reliable. For me, there wasn't anything new in 2019 that I needed or wanted.

 

[Logit]

.
Played around with a few things after doing some internet research. See if this helps on your end :

MAIN MACRO :

Option Explicit

Sub ImprtFiltr()
 
Dim str As String

Application.ScreenUpdating = False

With Sheet1
    If .Range("A1").Value <> "" Then
       .Range("A:C").EntireColumn.Delete
    End If
End With


 
'Delete existing HOEP_Historical
Sheets("HOEP_Historical").Activate 'Name of sheet the HOEP_Historical will be downloaded into. Change as required.
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
 
'Download stock quotes. Be patient - takes a few seconds.
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
            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:=1


Columns("A:A").Replace What:=" *", Replacement:="", LookAt:=xlPart
    Columns("A:A").TextToColumns Destination:=Range("A1"), _
    DataType:=xlDelimited, FieldInfo:=Array(1, xlYMDFormat)
    
With Sheets("HOEP_Historical")
    .Range("D:I").EntireColumn.Delete
    .Range("1:3").EntireRow.Delete
    .Columns("A:J").ColumnWidth = 12
    .Range("J1").Value = "Double Click"
End With


Columns("A:A").NumberFormat = "mm-dd-yyyy"

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

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


Range("A1").Select

Remove

Application.ScreenUpdating = True

MsgBox "Process Complete", vbInformation, ""
End Sub

Sub Remove()
Dim connection As WorkbookConnection
On Error Resume Next
'Clear history of Connections

For Each connection In ThisWorkbook.Connections
    connection.Delete
Next

End Sub

SHEET MACRO :

Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
        If Not Intersect(Target, Range("J1")) Is Nothing Then
            Call ImprtFiltr
        End If
End Sub

As you will see from the workbook download, you'll need to double-click on cell J1 to run the macro .

Import CSV.xlsm

 

[alex_peng]

Thank

s. I tried in my spreadsheet and no dice. I downloaded yours and it doesn't give me any errors but it is still doing that funky scrunch thing with rows. Also date doesn't look like it changed to the 7th. Somethings gotta be up with my office or PC. I tried in a Vm with another version (2007) just to make sure and it comes up with the same. Is there a way to remove any formatting that could effect row height? Im grasping now.