Validation and Highlighting & copying columns, using VBA

Truculent Llama

New Member
Joined
Mar 18, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,179
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
This might get you going in the right direction
VBA Code:
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
 
Solution

Truculent Llama

New Member
Joined
Mar 18, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
This might get you going in the right direction
VBA Code:
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:
Code:
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

Well-known Member
Joined
Oct 27, 2005
Messages
20,179
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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
VBA Code:
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

New Member
Joined
Mar 18, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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 :p

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 👂👂👂👂
VBA Code:
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

Well-known Member
Joined
Oct 27, 2005
Messages
20,179
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
UNTESTED
VBA Code:
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

New Member
Joined
Mar 18, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Oct 27, 2005
Messages
20,179
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
VBA Code:
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
VBA Code:
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

New Member
Joined
Mar 18, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
VBA Code:
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

Well-known Member
Joined
Oct 27, 2005
Messages
20,179
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Won't work if there are less than 300 rows .. :biggrin:
BTW worked fine for me
 

Watch MrExcel Video

Forum statistics

Threads
1,130,112
Messages
5,640,174
Members
417,130
Latest member
Darion2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top