Compare cell in column to cells below

S

Shadkng

Active Member
Joined
Oct 11, 2018
Messages
370
Hi, the following code loops through a number of columns to compare the cell value in row 30 to the values below down to last row. If all of the values are the same it will insert the word HIDE in row 27 for each column. I need help to modify the code so that blank cells are ignored in the columns and only cells that have a value are considered. Currently, if I insert a blank line, the criteria won't be met that all cells will match the top cell. I hope this is clear. Thanks for the help.

VBA Code: 
Sub DetailColumHide()
' Macro to populate the row-27 w/ "HIDE" if all categories in the column are the same (excluding accessories)

Worksheets("DETAIL FORM").Select
Dim lr As Long 'last row
Dim cat As Variant 'category
Dim x As Long 'loop counter variable
Dim y As Long 'loop counter variable
Dim hide As Long 'counter for category in column loop

lr = Worksheets("DETAIL FORM").Cells(Rows.Count, 7).End(xlUp).Row - 1 'find last cell used in Col.G (7)
If lr < 30 Then Exit Sub
lr = lr - Range("B2").Value 'subtract "NUMBER OF ACCESS LINES >>" to NOT count accessories

For x = 9 To 27 'Col.I to Col.AA  12/31/19 Steve changed to "9 to 26", and col AA to Z.  Reason is col AA should always hide.
    cat = ActiveSheet.Cells(30, x)
    hide = 0
    For y = 31 To lr
        If ActiveSheet.Cells(y, x).Value = cat Then hide = hide + 1
    Next y
    
    If hide + 30 = lr Then
        ActiveSheet.Cells(27, x).Value = "HIDE"
    Else
        ActiveSheet.Cells(27, x).Value = ""
    End If
Next x


End Sub
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
Try changing this line:
VBA Code: 
If ActiveSheet.Cells(y, x).Value = cat Then hide = hide + 1

to this:
VBA Code: 
If ActiveSheet.Cells(y, x).Value = cat Or Len(ActiveSheet.Cells(y, x)) = 0 Then hide = hide + 1

Incidentally, this:
VBA Code: 
lr = Worksheets("DETAIL FORM").Cells(Rows.Count, 7).End(xlUp).Row - 1 'find last cell used in Col.G (7)
Does not find the last cell used in column G - it finds the last cell minus one. I would lose the -1 if I were you.
 
Upvote 0
kevin9999 said:
Try changing this line:
VBA Code: 
If ActiveSheet.Cells(y, x).Value = cat Then hide = hide + 1

to this:
VBA Code: 
If ActiveSheet.Cells(y, x).Value = cat Or Len(ActiveSheet.Cells(y, x)) = 0 Then hide = hide + 1

Incidentally, this:
VBA Code: 
lr = Worksheets("DETAIL FORM").Cells(Rows.Count, 7).End(xlUp).Row - 1 'find last cell used in Col.G (7)
Does not find the last cell used in column G - it finds the last cell minus one. I would lose the -1 if I were you.
Click to expand...
Hi, using the following new line partially helped. When the entire column was blank, it moved HIDE correctly. When there is a value in a cell in row 30, and the rest were blank it still moved HIDE, which it should not. However, when the cell in row 30 was blank, but there was a value in any other cell in the column HIDE appeared which is correct.
 
Upvote 0
Try the following instead (untested)

VBA Code: 
If Len(ActiveSheet.Cells(y, x)) <> 0 And ActiveSheet.Cells(y, x).Value = cat Then Hide = Hide + 1
 
Upvote 0
Ignore last post. Change this section:
VBA Code: 
For x = 9 To 27 'Col.I to Col.AA  12/31/19 Steve changed to "9 to 26", and col AA to Z.  Reason is col AA should always hide.
    cat = ActiveSheet.Cells(30, x)
    hide = 0
    For y = 31 To lr
        If ActiveSheet.Cells(y, x).Value = cat Then hide = hide + 1
    Next y
    
    If hide + 30 = lr Then
        ActiveSheet.Cells(27, x).Value = "HIDE"
    Else
        ActiveSheet.Cells(27, x).Value = ""
    End If
Next x

To this:
VBA Code: 
For x = 9 To 27 'Col.I to Col.AA  12/31/19 Steve changed to "9 to 26", and col AA to Z.  Reason is col AA should always hide.
    cat = ws.Cells(30, x)
    hide = 0
    For y = 31 To lr
        If ActiveSheet.Cells(y, x).Value = cat Or Len(ActiveSheet.Cells(y, x)) = 0 Then hide = hide + 1
    Next y
    
    If WorksheetFunction.CountA(Range(Cells(31, x), Cells(lr, x))) > 0 And hide + 30 = lr Then
        ws.Cells(27, x).Value = "HIDE"
    Else
        ws.Cells(27, x).Value = ""
    End If
Next x
 
Upvote 0
kevin9999 said:
Ignore last post. Change this section:
VBA Code: 
For x = 9 To 27 'Col.I to Col.AA  12/31/19 Steve changed to "9 to 26", and col AA to Z.  Reason is col AA should always hide.
    cat = ActiveSheet.Cells(30, x)
    hide = 0
    For y = 31 To lr
        If ActiveSheet.Cells(y, x).Value = cat Then hide = hide + 1
    Next y
   
    If hide + 30 = lr Then
        ActiveSheet.Cells(27, x).Value = "HIDE"
    Else
        ActiveSheet.Cells(27, x).Value = ""
    End If
Next x

To this:
VBA Code: 
For x = 9 To 27 'Col.I to Col.AA  12/31/19 Steve changed to "9 to 26", and col AA to Z.  Reason is col AA should always hide.
    cat = ws.Cells(30, x)
    hide = 0
    For y = 31 To lr
        If ActiveSheet.Cells(y, x).Value = cat Or Len(ActiveSheet.Cells(y, x)) = 0 Then hide = hide + 1
    Next y
   
    If WorksheetFunction.CountA(Range(Cells(31, x), Cells(lr, x))) > 0 And hide + 30 = lr Then
        ws.Cells(27, x).Value = "HIDE"
    Else
        ws.Cells(27, x).Value = ""
    End If
Next x
Click to expand...

Got a debug message "run time error 424, object required. Below was hi-lighted.

cat = ws.Cells(30, x)
 
Upvote 0
Sorry, forgot I'd set a variable for your sheet in my test file. Try this instead:
VBA Code: 
For x = 9 To 27 'Col.I to Col.AA  12/31/19 Steve changed to "9 to 26", and col AA to Z.  Reason is col AA should always hide.
    cat = ActiveSheet.Cells(30, x)
    hide = 0
    For y = 31 To lr
        If ActiveSheet.Cells(y, x).Value = cat Or Len(ActiveSheet.Cells(y, x)) = 0 Then hide = hide + 1
    Next y
    
    If WorksheetFunction.CountA(Range(Cells(31, x), Cells(lr, x))) > 0 And hide + 30 = lr Then
        ActiveSheet.Cells(27, x).Value = "HIDE"
    Else
        ActiveSheet.Cells(27, x).Value = ""
    End If
Next x
 
Upvote 0
Same result as the solution from message 4. I can give you a little more info:

As before, if the top cell (row 30) has a value, HIDE does not result. If the top cell AND another cell in the column has a value it does not work either. If the top cell is blank and any other cell in the column has a value, then it works and HIDE appears correctly.
 
Upvote 0
We could go back and forth indefinitely, or we could save a lot of time if you could post your actual sheet using the XL2BB add-in, or alternatively share your file via Google Drive, Dropbox or similar file sharing platform?
 
Upvote 0
kevin9999 said:
We could go back and forth indefinitely, or we could save a lot of time if you could post your actual sheet using the XL2BB add-in, or alternatively share your file via Google Drive, Dropbox or similar file sharing platform?
Click to expand...
I wish I could, but this section is a small part of a huge sheet and it depends on other sheets to work. You got it almost working though.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Could not set the RowSource property. Invalid property value. Error 380
2
Replies
16
Views
205
RoryA
RoryA
M
  • Solved
run formula based on cell value to the last row
Replies
5
Views
381
MMM_84
M
N
  • Question Question
CountIFS, conditional formatting and ignoring hidden columns
Replies
0
Views
68
NewbieExcel123
N
A
  • Solved
Auto Serial Number in a Cell
2 3
Replies
23
Views
951
arijitirf
A
K
  • Solved
Previously working very complex VBA now stopped
Replies
7
Views
532
Alex Blakenburg
A

Forum statistics

Threads
1,223,880
Messages
6,175,154
Members
452,615
Latest member
bogeys2birdies

Share this page

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
Back
Top