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
 
Sort by date Sort by votes
Anyway, my logic on this is not right. Let me come back to you if I think there could be a solution. Thanks for your help so far.
 
Upvote 0

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Difficult without seeing your actual data, but here's another try. The following code (replace your entire macro)
VBA Code: 
Option Explicit
Sub Test_V2()
    Dim ws As Worksheet
    Set ws = Sheets("DETAIL FORM")
    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 = ws.Cells(Rows.Count, 7).End(xlUp).Row 'find last cell used in Col.G (7)
    If lr < 30 Then Exit Sub
    lr = lr - Range("B2").Value '**** NB: put value of 2 in B2 for the purpose of testing
    
    For x = 9 To 27
        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
End Sub

will turn this data layout:
Shadkng.xlsm
GHIJKLMNOPQRSTUVWXYZAA
27column G
28
29
30somethingcolumn 9column 10column 11column 12column 13column 15column 16column 17column 18column 20column 21column 22column 23column 24column 26
31somethingcolumn 10column 12column 13column 14column 15column 16column 17column 18column 21column 22column 23column 24column 26column 27
32somethingcolumn 10column 12column 13column 14column 15column 17column 18column 21column 22column 23column 24column 26column 27
33somethingdifferentcolumn 12column 13column 14column 15column 17column 18column 21differentdifferentcolumn 24column 26column 27
34somethingcolumn 10column 12column 13column 14column 15column 17column 18column 20column 21column 22column 23column 24column 26column 27
35somethingcolumn 10column 12column 13column 14column 15column 16column 17column 18column 20column 21column 22column 23column 24differentcolumn 27
36somethingcolumn 10column 12column 13column 14column 15column 16column 17column 18column 20column 21column 22column 23column 24column 26column 27
37somethingcolumn 10column 12column 13column 14column 15column 16column 17column 18column 20column 21column 22column 23column 24column 26column 27
38
DETAIL FORM


Into this:
Shadkng.xlsm
GHIJKLMNOPQRSTUVWXYZAA
27column GHIDEHIDEHIDEHIDEHIDEHIDEHIDEHIDEHIDE
28
29
30somethingcolumn 9column 10column 11column 12column 13column 15column 16column 17column 18column 20column 21column 22column 23column 24column 26
31somethingcolumn 10column 12column 13column 14column 15column 16column 17column 18column 21column 22column 23column 24column 26column 27
32somethingcolumn 10column 12column 13column 14column 15column 17column 18column 21column 22column 23column 24column 26column 27
33somethingdifferentcolumn 12column 13column 14column 15column 17column 18column 21differentdifferentcolumn 24column 26column 27
34somethingcolumn 10column 12column 13column 14column 15column 17column 18column 20column 21column 22column 23column 24column 26column 27
35somethingcolumn 10column 12column 13column 14column 15column 16column 17column 18column 20column 21column 22column 23column 24differentcolumn 27
36somethingcolumn 10column 12column 13column 14column 15column 16column 17column 18column 20column 21column 22column 23column 24column 26column 27
37somethingcolumn 10column 12column 13column 14column 15column 16column 17column 18column 20column 21column 22column 23column 24column 26column 27
38
DETAIL FORM


If this is not right, then I really need a more comprehensive explanation, as well as your actual file as I need to see your actual data layout (it won't be too big to share via a file sharing platform).
 
Upvote 0
Solution
kevin9999 said:
Difficult without seeing your actual data, but here's another try. The following code (replace your entire macro)
VBA Code: 
Option Explicit
Sub Test_V2()
    Dim ws As Worksheet
    Set ws = Sheets("DETAIL FORM")
    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 = ws.Cells(Rows.Count, 7).End(xlUp).Row 'find last cell used in Col.G (7)
    If lr < 30 Then Exit Sub
    lr = lr - Range("B2").Value '**** NB: put value of 2 in B2 for the purpose of testing
   
    For x = 9 To 27
        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
End Sub

will turn this data layout:
Shadkng.xlsm
GHIJKLMNOPQRSTUVWXYZAA
27column G
28
29
30somethingcolumn 9column 10column 11column 12column 13column 15column 16column 17column 18column 20column 21column 22column 23column 24column 26
31somethingcolumn 10column 12column 13column 14column 15column 16column 17column 18column 21column 22column 23column 24column 26column 27
32somethingcolumn 10column 12column 13column 14column 15column 17column 18column 21column 22column 23column 24column 26column 27
33somethingdifferentcolumn 12column 13column 14column 15column 17column 18column 21differentdifferentcolumn 24column 26column 27
34somethingcolumn 10column 12column 13column 14column 15column 17column 18column 20column 21column 22column 23column 24column 26column 27
35somethingcolumn 10column 12column 13column 14column 15column 16column 17column 18column 20column 21column 22column 23column 24differentcolumn 27
36somethingcolumn 10column 12column 13column 14column 15column 16column 17column 18column 20column 21column 22column 23column 24column 26column 27
37somethingcolumn 10column 12column 13column 14column 15column 16column 17column 18column 20column 21column 22column 23column 24column 26column 27
38
DETAIL FORM


Into this:
Shadkng.xlsm
GHIJKLMNOPQRSTUVWXYZAA
27column GHIDEHIDEHIDEHIDEHIDEHIDEHIDEHIDEHIDE
28
29
30somethingcolumn 9column 10column 11column 12column 13column 15column 16column 17column 18column 20column 21column 22column 23column 24column 26
31somethingcolumn 10column 12column 13column 14column 15column 16column 17column 18column 21column 22column 23column 24column 26column 27
32somethingcolumn 10column 12column 13column 14column 15column 17column 18column 21column 22column 23column 24column 26column 27
33somethingdifferentcolumn 12column 13column 14column 15column 17column 18column 21differentdifferentcolumn 24column 26column 27
34somethingcolumn 10column 12column 13column 14column 15column 17column 18column 20column 21column 22column 23column 24column 26column 27
35somethingcolumn 10column 12column 13column 14column 15column 16column 17column 18column 20column 21column 22column 23column 24differentcolumn 27
36somethingcolumn 10column 12column 13column 14column 15column 16column 17column 18column 20column 21column 22column 23column 24column 26column 27
37somethingcolumn 10column 12column 13column 14column 15column 16column 17column 18column 20column 21column 22column 23column 24column 26column 27
38
DETAIL FORM


If this is not right, then I really need a more comprehensive explanation, as well as your actual file as I need to see your actual data layout (it won't be too big to share via a file sharing platform).
Click to expand...
Kevin, thank you it worked. Your grid was basically the same concept. Thanks for taking the time, I appreciate it.
 
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
232
RoryA
RoryA
M
  • Solved
run formula based on cell value to the last row
Replies
5
Views
387
MMM_84
M
N
  • Question Question
CountIFS, conditional formatting and ignoring hidden columns
Replies
0
Views
74
NewbieExcel123
N
A
  • Solved
Auto Serial Number in a Cell
2 3
Replies
23
Views
953
arijitirf
A
K
  • Solved
Previously working very complex VBA now stopped
Replies
7
Views
532
Alex Blakenburg
A

Forum statistics

Threads
1,223,941
Messages
6,175,537
Members
452,652
Latest member
eduedu

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