Data Validation Cell Length with error message

New Contributor



I am currently creating a template for suppliers where they have to fill in product information. 

In order to avoid receiving bad quality data, I would like to create a macro that the suppliers can use to check if the values are entered correctly before sending me back the file. 

I do not want to use simple data validation, because some do mass upload and can fill in the cell avoid the data validation rules. 

To be clear, I would like a macro that when is activated, is check all the cell (from X21 to X7100, Y21 to Y7100 and Z21 to Z100) and state an error message when the length of the cell is over 32. 

Additional informations:

The name of the sheet is "(3)Product Template" and the workbook is "Product Template 2022"

Not all the 7100 rows are filled in, they are fulfilled depending on the number of article the supplier has.


I tried several macro's found on internet but none of them were working for me (as I cannot code vba, I guess they worked but I am trash at adapting them to my file). 


If anyone could help me, you would save my day!

2 Replies
best response confirmed by Hans Vogelaar (MVP)


Sub data()

Dim i As Long
Dim j As Long

For i = 21 To 7100
For j = 24 To 26

If Len(Cells(i, j)) > 32 Then
MsgBox ("Error in column:" & j & "row:" & i)
End If

Next j

Next i

End Sub

You can try this code. In the attached file you can click the button in cell V2 to run the macro. If a cell is longer than 32 digits an error message shows the row and column number of that cell.

Thank you very much for your help! Saved my day!