ExcelSujeet (YouTube Blogger)

1) Range("a1").AutoFilter Field:=int_Status, Criteria1:="Open" 2) Filter US Format Dates Range("a1").AutoFilter Field:=int_Due, Criteria1:="<" & Format(Date, "mm-dd-yyyy") 3) Filter US Format Dates and Blank Cells  Range("a1").AutoFilter Field:=int_Due, Criteria1:="<" & Format(Date, "mm-dd-yyyy"), Operator:=xlOr, Criteria2:="=" 4) Filter US Format Dates and Non-Blank Cells  Range("a1").AutoFilter Field:=int_Due, Criteria1:="<" & Format(Date, "mm-dd-yyyy"), Operator:=xlOr, Criteria2:="<>" 5) Filter Multiple Values Range("a1").AutoFilter Field:=5, Criteria1:=Array("10", "20","59"), Operator:=xlFilterValues

  FSO - Check If a file exists, if exists then delete the file. Dim fso As Scripting.FileSystemObject Sub test() Application.ScreenUpdating = False Set fso = New Scripting.FileSystemObject Dim flname As String flname = "D:\Excel Practice\Test.txt" If Not fso.FileExists(flname) Then Exit Sub Else 'flname.Delete--------Incorrect fso.DeleteFile (flname) '---Correct MsgBox "file deleted", vbInformation End If Application.ScreenUpdating = True End Sub '--------------------------------------------- Sub DeleteAllFilesFromAFolder() Dim fso As Object Dim fd As Object Dim fl As Object Dim str_Path As String str_Path = "D:\New Folder\" Set fso = CreateObject("Scripting.FileSystemObject") Set fd = fso.getfolder(str_Path) For Each fl In fd.Files     fl.Delete Next fl End Sub Delete a file without using Loop If obj_fso.fileexists(ThisWorkbook.Path & "\" & "xyz.xlsx") Then obj_fso.Deletefile ThisWorkbook.Path & ...

 Sometimes we have to separate multiple row data from a cell and the data in a cell is separated by line breaks. We can do this using the text-to-columns feature available in excel.    

 Pivot Table - Assuming the Data and the Pivot table both are on Sheet1 in the workbook. Sub PivotTableChart() Dim pc As PivotCache Dim pt As PivotTable Dim lrow As Integer Dim lcol As Integer Dim rng As Range ThisWorkbook.Activate Sheet1.Activate lrow = Cells(Rows.Count, 1).End(xlUp).Row lcol = Cells(1, Columns.Count).End(xlToLeft).Column 'Debug.Print lrow & "-" & lcol Set rng = Range(Cells(1, 1), Cells(lrow, lcol)) 'Debug.Print rng.Address Set pc = ThisWorkbook.PivotCaches.Create(xlDatabase, rng, 6) Set pt = pc.CreatePivotTable(Sheet1.Range("z5"), "MyPivot") With Sheet1.PivotTables("MyPivot")     With .PivotFields("Location")             .Orientation = xlRowField             .Position = 1     End With      '    .AddDataField Sheet1.PivotTables("MyPivot").PivotFields("InsuredValue"), _ '    "Sum of InsuredValue", xlSum     With .PivotFields("Insured...

How to install Selenium? Click the below link to know the steps. https://learnexcelwithsujeet.blogspot.com/2021/05/steps-to-install-and-use-selenium-for.html Example-1 Sub test() Dim ch As Selenium.ChromeDriver Dim ts As Selenium.WebElements Set ch = New Selenium.ChromeDriver ch.Start ch.Get "https://www.indiatoday.in/education-today/gk-current-affairs/story/indian-states-with-highest-population-1358414-2018-10-08" Set ts = ch.FindElementsByCss("table") Debug.Print ts.Count ts(1).AsTable.ToExcel ThisWorkbook.Sheets(1).Range("a1") End Sub

 Example-1 Multiselect:=True, let you select multiple files in one go and store the location of all files in a variable and let you open one file or workbook at one time. Note: It is important to note that the type of variable holding file or files name should be Variant. Sub OpenFile() On Error Resume Next Dim str As Variant Dim i As Integer str = Application.GetOpenFilename(MultiSelect:=True) Debug.Print str For i = LBound(str) To UBound(str) Debug.Print str(i) 'Workbooks.Open str(i) 'Debug.Print ActiveWorkbook.Name 'ActiveWorkbook.Close Next End Sub

  Sub test() 'MkDir "D:\Test\" 'MkDir "D:\Rust\" 'FileCopy "D:\Test\txt.txt", "D:\Test\txt-2.txt" 'Kill "D:\Test\txt-2.txt" Name "D:\Test\txt.txt" As "D:\Rust\txt.txt" End Sub Sub Movefile() Dim fso As Scripting.FileSystemObject Set fso = New Scripting.FileSystemObject fso.Movefile "D:\Rust\txt.txt", "D:\Test\txt.txt" End Sub Create Text File and Read Data in Excel Line by Line. Sub CreateTextFile() Dim fso As Scripting.FileSystemObject Set fso = New Scripting.FileSystemObject Dim txt As Scripting.TextStream Dim i As Integer i = 1 Set txt = fso.CreateTextFile("D:\Test\txt-3.txt", True) txt.WriteLine "This is first line" txt.WriteBlankLines (1) txt.WriteLine "This is second line" 'Set txt = fso.CreateTextFile("D:\Text\txt-4.txt", True) Set txt = fso.OpenTextFile("D:\Test\txt-3.txt") Do Until txt.AtEndOfStream Cells(i, 1) = txt.Re...