Geek Answers Handbook

Put the entire column (each value in the column) in an array?

So, I am doing a macro to do a bunch of things. one thing is to find duplicate cells in sheet1 from sheet2. in this column A in sheet 1, any values ​​in column B on sheet2 correspond to any of the values ​​in column columna1.

I know that theres removes duplicates, but I just want to mark them, not delete.

I was thinking something about filtering. I know that when filtering you can select several criteria, therefore, if you have a column with 20 different values, you can select 5 values ​​in the filter and display rows with these 5 values ​​for a specific column. So I wrote a macro of this and checked the code, and I see that it uses a string array, where each value to search is in an array of strings. Is there a way to just specify the full column and add each value to an array of rows?

early

+6
source share
2 answers

Here are three ways to load items into an array. The first method is much faster, but just stores everything in a column. You have to be careful with this because it creates a multidimensional array that cannot be passed to AutoFilter.

Method 1:

Sub LoadArray() Dim strArray As Variant Dim TotalRows As Long TotalRows = Rows(Rows.Count).End(xlUp).Row strArray = Range(Cells(1, 1), Cells(TotalRows, 1)).Value MsgBox "Loaded " & UBound(strArray) & " items!" End Sub

Method 2:

 Sub LoadArray2() Dim strArray() As String Dim TotalRows As Long Dim i As Long TotalRows = Rows(Rows.Count).End(xlUp).Row ReDim strArray(1 To TotalRows) For i = 1 To TotalRows strArray(i) = Cells(i, 1).Value Next MsgBox "Loaded " & UBound(strArray) & " items!" End Sub

if you know the values ​​ahead of time and just want to list them in a variable, you can assign a variant using Array ()

 Sub LoadArray3() Dim strArray As Variant strArray = Array("Value1", "Value2", "Value3", "Value4") MsgBox "Loaded " & UBound(strArray) + 1 & " items!" End Sub
+13
source

Not sure if anyone will have this problem or not, so I decided that I would send the answer I found. I like the array solution sent by @Ripster (and thanks for that, it almost worked), but in this case it will not work. I am working with a large data sheet with 1 identifier column, and I want to check other sheets to see if there are duplicates on this sheet (using the ID column). do not delete, just check so I can check them out. With potentially above 50K lines passing through each line, it will take LONG time.

So, I realized what I can do is copy the ID column from another sheet to the main sheet and use the conditional formatting option to mark duplicates in some color. (It will mark the rows in both columns) and then I can filter the column by color to show me only the color that I used to mark duplicates. If I programmatically add a column to the sheet, I check the row numbers, I can even include this column in the main sheet, so when I filter the color, I can see which rows they were on my sheet.

After that I can record and adapt the macro to do it automatically for my less programmable oblique employees

Thanks everyone!

Edit - Added code

After selecting the columns to compare, here is the code for marking duplicates in red text and without filling. - Selection.FormatConditions.AddUniqueValues Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority Selection.FormatConditions(1).DupeUnique = xlDuplicate With Selection.FormatConditions(1).Font .Color = -16383844 .TintAndShade = 0 End With Selection.FormatConditions(1).StopIfTrue = False

and then, since both columns have marked duplicates, you select the one you actually want to examine and has a filter to filter:

 `Selection.AutoFilter ActiveSheet.Range("$C$1:$C$12").AutoFilter Field:=1, Criteria1:=RGB(156, 0 _ , 6), Operator:=xlFilterFontColor`

(in my test, I used column c as a filter for filtering, which can be programmatic with cells() or range(cells(), cells())

I wish everyone good luck in their future beginners! Thanks again @ripster

+1
source

More articles:


All Articles