Jan 17 2023 01:01 PM
Please post the code of the macro.
It might also help if you could attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar.
Jan 18 2023 01:37 AM
Sub Macro1() ' ' Macro1 Macro '
' Range("A1").Select ActiveSheet.Paste Selection.Hyperlinks.Delete Application.CutCopyMode = False ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$A$24"), , xlNo).Name = _ "Table1" Range("Table1[[#All],[Column1]]").Select ActiveWorkbook.Queries.Add Name:="Table1", Formula:= _ "let" & Chr(13) & "" & Chr(10) & " Source = Excel.CurrentWorkbook(){[Name=""Table1""]}[Content]," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Source,{{""Column1"", type any}})," & Chr(13) & "" & Chr(10) & " #""Split Column by Delimiter"" = Table.SplitColumn(Table.TransformColumnTypes(#""Changed Type"", {{""Column1"", type text}}, ""en-US""), ""Column1"", Splitter.SplitTextByDelimiter("" "", QuoteStyle.Csv), {" & _ """Column1.1"", ""Column1.2"", ""Column1.3"", ""Column1.4"", ""Column1.5"", ""Column1.6"", ""Column1.7"", ""Column1.8"", ""Column1.9"", ""Column1.10"", ""Column1.11"", ""Column1.12"", ""Column1.13"", ""Column1.14"", ""Column1.15"", ""Column1.16"", ""Column1.17"", ""Column1.18"", ""Column1.19"", ""Column1.20"", ""Column1.21"", ""Column1.22"", ""Column1.23""})," & Chr(13) & "" & Chr(10) & " #" & _ """Changed Type1"" = Table.TransformColumnTypes(#""Split Column by Delimiter"",{{""Column1.1"", type text}, {""Column1.2"", type text}, {""Column1.3"", type text}, {""Column1.4"", type text}, {""Column1.5"", type text}, {""Column1.6"", type text}, {""Column1.7"", type text}, {""Column1.8"", type text}, {""Column1.9"", type text}, {""Column1.10"", type text}, {""Colum" & _ "n1.11"", type text}, {""Column1.12"", type text}, {""Column1.13"", type text}, {""Column1.14"", type text}, {""Column1.15"", type text}, {""Column1.16"", type text}, {""Column1.17"", type text}, {""Column1.18"", type text}, {""Column1.19"", type text}, {""Column1.20"", type text}, {""Column1.21"", type text}, {""Column1.22"", type text}, {""Column1.23"", type text}}" & _ ")," & Chr(13) & "" & Chr(10) & " #""Split Column by Delimiter1"" = Table.SplitColumn(#""Changed Type1"", ""Column1.1"", Splitter.SplitTextByDelimiter(""#"", QuoteStyle.Csv), {""Column1.1.1"", ""Column1.1.2""})," & Chr(13) & "" & Chr(10) & " #""Changed Type2"" = Table.TransformColumnTypes(#""Split Column by Delimiter1"",{{""Column1.1.1"", type text}, {""Column1.1.2"", Int64.Type}})," & Chr(13) & "" & Chr(10) & " #""Removed Other Columns"" =" & _ " Table.SelectColumns(#""Changed Type2"",{""Column1.1.1"", ""Column1.1.2""})," & Chr(13) & "" & Chr(10) & " #""Filtered Rows"" = Table.SelectRows(#""Removed Other Columns"", each ([Column1.1.1] <> ""0.36805555555555558"" and [Column1.1.1] <> ""0.37013888888888885"" and [Column1.1.1] <> ""0.37083333333333335"" and [Column1.1.1] <> ""0.37222222222222223""))" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Filtered Rows""" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Table1;Extended Properties=""""" _ , Destination:=Range("$A$1")).QueryTable .CommandType = xlCmdSql .CommandText = Array("SELECT * FROM [Table1]") .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = "Table1_2" .Refresh BackgroundQuery:=False End With End Sub
Jan 18 2023 01:39 AM
Jan 18 2023 01:43 AM
Thanks, but that is difficult to read. Could you please use the </> button on the toolbar to insert the code? Thanks in advance.
Jan 18 2023 02:48 AM
Jan 18 2023 03:31 AM
Thanks very much, that is useful. I'm afraid I cannot help you with this, but others seeing this discussion should be able to look at your macro and come up with a suggestion.