Compare cell in column to cells below

S

Shadkng

Active Member
Joined
Oct 11, 2018
Messages
365
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

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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

K
  • Question
Previously working very complex VBA now stopped
Replies
1
Views
32
Alex Blakenburg
A
K
  • Solved
Update to existing Move data macro
2
Replies
11
Views
317
Alex Blakenburg
A
V
  • Question
VBA: Clear Contents for Multiple Groups of Columns based on Data
Replies
3
Views
266
VBA Beginner 2024
V
J
  • Question
Repeat VBA for each column
Replies
3
Views
225
Alex Blakenburg
A
S
  • Solved
Compare column A with several columns and copy the non-existent one
Replies
3
Views
195
Trebor76
T

Forum statistics

Threads
1,215,071
Messages
6,122,963
Members
449,094
Latest member
Anshu121

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