Validation and Highlighting & copying columns, using VBA

[Truculent Llama]

Hi all,
I have a very large spreadsheet (currently at 130k+ rows and growing), which I'd like to perform a few functions on:
1. validate the fields within one of the columns, and alert the user if there are invalid entries
2. remove duplicate entries, keeping bottom rows instead of top rows

Request #1
My list of valid values to compare against is 136 values.
I have sought help from other users on Reddit r/excel and have had lots of help, however the validation involved a looping solution (and another a loop within a loop) to check through each cell in the column, and compare it against the list of valid options. There were all sorts of issues I encountered because of the large data set, and in the end I ended up settling on Excel conditional formatting to highlight invalid cells (which by comparison is instantaneous), and then sorting the data based on coloured fill. My macro also selects the last cell in the column, hopefully drawing the user's eye to the shaded cells. So far so good.
What I would like is for a quick VBA script that can assess whether or not any of those cells have been conditionally formatted, and if so throw an error message to alert the user. The MsgBox I'm fine with. It's the checking for the error that I can't work out.

Request #2
The way the data is managed, sometimes we need to overwrite certain rows with new data. Using a solution like VLOOKUP would be too cumbersome for the workflow in place, so what I'd like to implement is a simple solution to create a helper column to sort based on "newest" to "oldest", then simply remove duplicates top to bottom.
The data does not contain date or time columns. What I thought might be the easiest would be to:
1. Helper column has a simple count (+1 based on the row above)
2. Turn off auto calculation
3. Force a recalculation on the helper column with Application.Calculate
4. Sort the data descending
5. Remove duplicates
6. Turn auto calculation back on

So I think during the writing of this post, I've basically answered my own Request #2, but would love some help with Request #1.
Having said that, if anyone has any great suggestions for either, I'm open to ideas.

Thanking you in advance,
Llama

 

[Michael M]

This might get you going in the right direction

Sub MM1()
'original code from sitestory.dk
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row ' assumes data is in Col "A"
For r = 2 To lr
    If Cells(r, 1).FormatConditions.Count > 0 Then
       MsgBox Cells(r, 1).Address & " has conditional formatting."
    End If
 Next r
End Sub

 

[Truculent Llama]

This might get you going in the right direction

Sub MM1()
'original code from sitestory.dk
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row ' assumes data is in Col "A"
For r = 2 To lr
    If Cells(r, 1).FormatConditions.Count > 0 Then
       MsgBox Cells(r, 1).Address & " has conditional formatting."
    End If
Next r
End Sub

Thanks Michael,

But I'm having trouble working out how to make it check column B instead. Changing the code to lr = Cells(Rows.Count, "B").End(xlUp).Row didn't work, but I can't see any other reference to columns anywhere.

Also, it would be quicker if I could set the macro to check only the last n rows, as I have a macro to sort on colour already, and there should never be that many errors (well...)

If I were to define my range as column B, last row to (last row -500), could I substitute a variable eg. FirstRowToCheck in for the "2" in the for loop? Like this:

For r = FirstRowToCheck To lr
    If Cells(r, 1).FormatConditions.Count > 0 Then
       MsgBox Cells(r, 1).Address & " has conditional formatting."
    End If
Next r

Would that work? Or is there a better way?

 

[Michael M]

If you want it to check Col "B" use
Note the reference now to column"2" instead of "1" in the Cells lines inside the loop

Sub MM1()
'original code from sitestory.dk
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "B").End(xlUp).Row ' assumes data is in Col "A"
For r = 2 To lr
    If Cells(r, 2).FormatConditions.Count > 0 Then
       MsgBox Cells(r, 2).Address & " has conditional formatting."
    End If
Next r
End Sub

 

[Truculent Llama]

If you want it to check Col "B" use
Note the reference now to column"2" instead of "1" in the Cells lines inside the loop

?‍
I feel like I just looked up a walkthrough for an old school point-and-click adventure game... and after reading it realised I was being incredibly dumb. Thank you for pointing it out

There are two other things I'd like to do. Depending on how slow the first is to run, I may not need the second.
1. I'd like to have a single error message at the end IF any of the cells are conditionally formatted.
2. I'd like to be able to check only a range of n-bottom cells, with a variable n. This would be in an effort to speed up the process somewhat.

I've worked out a way to cannibalise some other code I've found online to achieve #1, and it seems to work. However if anyone can suggest a better way, I'm all ears ????

Sub MM1()

Dim lr As Long, r As Long: lr = Cells(Rows.Count, "B").End(xlUp).row
Dim iserror As Boolean: iserror = False

  For r = 2 To lr
    If Cells(r, 2).FormatConditions.Count > 0 Then
      iserror = True
    End If
  Next r

  If iserror Then
    MsgBox ("Errors have been found")
  End If

End Sub

Regarding #2:
It appears my code for #1 is fine, and doesn't take too long, despite the enormous data set, so I'm not too fussed. By now it's just a point of curiosity of how I might achieve it than anything else.
- I've tried multiples variations of defining named ranges so they dynamically change (which works ok but would need to be manually updated from time to time).
- I've tried using variables to define the last row, and then my first row by lr-n, but then can't seem to use that range in a check macro.

Any suggestions?

 

[Michael M]

UNTESTED

Sub MM1()
Dim lr As Long, n As Integer, r As Long: lr = Cells(Rows.Count, "B").End(xlUp).Row
n = 0
For r = 2 To lr
    If Cells(r, 2).FormatConditions.Count > 0 Then
      n = n + 1
    End If
  Next r
  If n > 0 Then MsgBox ("There are Errors in the last " & lr - (lr - n) & "rows !")
End Sub

 

[Truculent Llama]

That looks like there's no reason why that wouldn't work.
But I may have been unclear.
What I'd like is to be able to define n as eg. 300, and then for the macro to check the last 300 rows. So regardless of how many rows exist, only check the last n rows.
As data is added by users, obviously the last row will change, but still to check only the last n rows.

 

[Michael M]

Sub MM1()
Dim lr As Long, n As Integer, x As Long, r As Long: lr = Cells(Rows.Count, "B").End(xlUp).Row
n = 0
x = 300 'change to suit
For r = x To lr
    If Cells(r, 2).FormatConditions.Count > 0 Then
      n = n + 1
    End If
  Next r
  If n > 0 Then MsgBox ("There are Errors in the last " & lr - (lr - n) & "rows !")
End Sub

OR if you wanted the user to decide, have an inputBox ask the user how many rows to check

Sub MM1()
Dim lr As Long, n As Integer, x As Long, r As Long: lr = Cells(Rows.Count, "B").End(xlUp).Row
n = 0
x = InputBox("How many rows do you want to check ??", "Row Checking")
For r = x To lr
    If Cells(r, 2).FormatConditions.Count > 0 Then
      n = n + 1
    End If
  Next r
  If n > 0 Then MsgBox ("There are Errors in the last " & lr - (lr - n) & "rows !")
End Sub

 

[Truculent Llama]

Sub MM1()
Dim lr As Long, n As Integer, x As Long, r As Long: lr = Cells(Rows.Count, "B").End(xlUp).Row
n = 0
x = 300 'change to suit
For r = x To lr
    If Cells(r, 2).FormatConditions.Count > 0 Then
      n = n + 1
    End If
  Next r
  If n > 0 Then MsgBox ("There are Errors in the last " & lr - (lr - n) & "rows !")
End Sub

Michael thanks again for your help. I did actually try this variation and it didn't seem to work. I'd run the macro and nothing happened... that I could see anyway, despite confirming there were errors.
I'll give it another go.

 

[Michael M]

Won't work if there are less than 300 rows ..
BTW worked fine for me