Saving the Row Number as a Variable for a Selected Cell

F

FirstTimeExcel1

New Member
Joined
May 12, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
First of all, don't laugh at me lol, I am new to VBA so I could have done the below steps much more easily and less redundant but given my lack of experience I decided to brute force it with a lot of lines of code. The part of the code that I need to edit is the "RowNum = 5". I want to be able to manually select the cell and then have the macro run, where it automatically saves the Row Number (RowNum) of that cell as the variable below. Any thoughts?

VBA Code: 
Sub HideColumns()
    RowNum = 5
        'Custom Brands
        If Cells(RowNum, 56).Value <> "FALSE" Then
            Cells(RowNum, 6).EntireColumn.Hidden = True
            Cells(RowNum, 7).EntireColumn.Hidden = True
            Cells(RowNum, 8).EntireColumn.Hidden = True
            Cells(RowNum, 9).EntireColumn.Hidden = True
        End If
        If Cells(RowNum, 56).Value = 1 Then
            Cells(RowNum, 6).EntireColumn.Hidden = False
            Cells(RowNum, 7).EntireColumn.Hidden = False
            Cells(RowNum, 8).EntireColumn.Hidden = False
            Cells(RowNum, 9).EntireColumn.Hidden = False
        End If
        'DR
        If Cells(RowNum, 57).Value <> "FALSE" Then
            Cells(RowNum, 10).EntireColumn.Hidden = True
            Cells(RowNum, 11).EntireColumn.Hidden = True
            Cells(RowNum, 12).EntireColumn.Hidden = True
            Cells(RowNum, 13).EntireColumn.Hidden = True
        End If
        If Cells(RowNum, 57).Value = 2 Then
            Cells(RowNum, 10).EntireColumn.Hidden = False
            Cells(RowNum, 11).EntireColumn.Hidden = False
            Cells(RowNum, 12).EntireColumn.Hidden = False
            Cells(RowNum, 13).EntireColumn.Hidden = False
        End If
        'ES
        If Cells(RowNum, 58).Value <> "FALSE" Then
            Cells(RowNum, 14).EntireColumn.Hidden = True
            Cells(RowNum, 15).EntireColumn.Hidden = True
            Cells(RowNum, 16).EntireColumn.Hidden = True
            Cells(RowNum, 17).EntireColumn.Hidden = True
        End If
        If Cells(RowNum, 58).Value = 3 Then
            Cells(RowNum, 14).EntireColumn.Hidden = False
            Cells(RowNum, 15).EntireColumn.Hidden = False
            Cells(RowNum, 16).EntireColumn.Hidden = False
            Cells(RowNum, 17).EntireColumn.Hidden = False
        End If
       'HO
        If Cells(RowNum, 59).Value <> "FALSE" Then
            Cells(RowNum, 18).EntireColumn.Hidden = True
            Cells(RowNum, 19).EntireColumn.Hidden = True
            Cells(RowNum, 20).EntireColumn.Hidden = True
            Cells(RowNum, 21).EntireColumn.Hidden = True
        End If
        If Cells(RowNum, 59).Value = 4 Then
            Cells(RowNum, 18).EntireColumn.Hidden = False
            Cells(RowNum, 19).EntireColumn.Hidden = False
            Cells(RowNum, 20).EntireColumn.Hidden = False
            Cells(RowNum, 21).EntireColumn.Hidden = False
        End If
       'HD
        If Cells(RowNum, 60).Value <> "FALSE" Then
            Cells(RowNum, 22).EntireColumn.Hidden = True
            Cells(RowNum, 23).EntireColumn.Hidden = True
            Cells(RowNum, 24).EntireColumn.Hidden = True
            Cells(RowNum, 25).EntireColumn.Hidden = True
        End If
        If Cells(RowNum, 60).Value = 5 Then
            Cells(RowNum, 22).EntireColumn.Hidden = False
            Cells(RowNum, 23).EntireColumn.Hidden = False
            Cells(RowNum, 24).EntireColumn.Hidden = False
            Cells(RowNum, 25).EntireColumn.Hidden = False
        End If
       'LA
        If Cells(RowNum, 61).Value <> "FALSE" Then
            Cells(RowNum, 26).EntireColumn.Hidden = True
            Cells(RowNum, 27).EntireColumn.Hidden = True
            Cells(RowNum, 28).EntireColumn.Hidden = True
            Cells(RowNum, 29).EntireColumn.Hidden = True
        End If
        If Cells(RowNum, 61).Value = 6 Then
            Cells(RowNum, 26).EntireColumn.Hidden = False
            Cells(RowNum, 27).EntireColumn.Hidden = False
            Cells(RowNum, 28).EntireColumn.Hidden = False
            Cells(RowNum, 29).EntireColumn.Hidden = False
        End If
        'LU
        If Cells(RowNum, 62).Value <> "FALSE" Then
            Cells(RowNum, 30).EntireColumn.Hidden = True
            Cells(RowNum, 31).EntireColumn.Hidden = True
            Cells(RowNum, 32).EntireColumn.Hidden = True
            Cells(RowNum, 33).EntireColumn.Hidden = True
        End If
        If Cells(RowNum, 62).Value = 7 Then
            Cells(RowNum, 30).EntireColumn.Hidden = False
            Cells(RowNum, 31).EntireColumn.Hidden = False
            Cells(RowNum, 32).EntireColumn.Hidden = False
            Cells(RowNum, 33).EntireColumn.Hidden = False
        End If
        'P2
        If Cells(RowNum, 63).Value <> "FALSE" Then
            Cells(RowNum, 34).EntireColumn.Hidden = True
            Cells(RowNum, 35).EntireColumn.Hidden = True
            Cells(RowNum, 36).EntireColumn.Hidden = True
            Cells(RowNum, 37).EntireColumn.Hidden = True
        End If
        If Cells(RowNum, 63).Value = 8 Then
            Cells(RowNum, 34).EntireColumn.Hidden = False
            Cells(RowNum, 35).EntireColumn.Hidden = False
            Cells(RowNum, 36).EntireColumn.Hidden = False
            Cells(RowNum, 37).EntireColumn.Hidden = False
        End If
        'PD
        If Cells(RowNum, 64).Value <> "FALSE" Then
            Cells(RowNum, 38).EntireColumn.Hidden = True
            Cells(RowNum, 39).EntireColumn.Hidden = True
            Cells(RowNum, 40).EntireColumn.Hidden = True
            Cells(RowNum, 41).EntireColumn.Hidden = True
        End If
        If Cells(RowNum, 64).Value = 9 Then
            Cells(RowNum, 38).EntireColumn.Hidden = False
            Cells(RowNum, 39).EntireColumn.Hidden = False
            Cells(RowNum, 40).EntireColumn.Hidden = False
            Cells(RowNum, 41).EntireColumn.Hidden = False
        End If
        'RO
        If Cells(RowNum, 65).Value <> "FALSE" Then
            Cells(RowNum, 42).EntireColumn.Hidden = True
            Cells(RowNum, 43).EntireColumn.Hidden = True
            Cells(RowNum, 44).EntireColumn.Hidden = True
            Cells(RowNum, 45).EntireColumn.Hidden = True
        End If
        If Cells(RowNum, 65).Value = 10 Then
            Cells(RowNum, 42).EntireColumn.Hidden = False
            Cells(RowNum, 43).EntireColumn.Hidden = False
            Cells(RowNum, 44).EntireColumn.Hidden = False
            Cells(RowNum, 45).EntireColumn.Hidden = False
        End If
        'SS
        If Cells(RowNum, 66).Value <> "FALSE" Then
            Cells(RowNum, 46).EntireColumn.Hidden = True
            Cells(RowNum, 47).EntireColumn.Hidden = True
            Cells(RowNum, 48).EntireColumn.Hidden = True
            Cells(RowNum, 49).EntireColumn.Hidden = True
        End If
        If Cells(RowNum, 66).Value = 11 Then
            Cells(RowNum, 46).EntireColumn.Hidden = False
            Cells(RowNum, 47).EntireColumn.Hidden = False
            Cells(RowNum, 48).EntireColumn.Hidden = False
            Cells(RowNum, 49).EntireColumn.Hidden = False
        End If
        'SP
        If Cells(RowNum, 67).Value <> "FALSE" Then
            Cells(RowNum, 50).EntireColumn.Hidden = True
            Cells(RowNum, 51).EntireColumn.Hidden = True
            Cells(RowNum, 52).EntireColumn.Hidden = True
            Cells(RowNum, 53).EntireColumn.Hidden = True
        End If
        If Cells(RowNum, 67).Value = 12 Then
            Cells(RowNum, 50).EntireColumn.Hidden = False
            Cells(RowNum, 51).EntireColumn.Hidden = False
            Cells(RowNum, 52).EntireColumn.Hidden = False
            Cells(RowNum, 53).EntireColumn.Hidden = False
        End If
End Sub
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
You just need to RowNum value for macro execution only, right?

You can create shortcut to run your macro like Ctrl+A etc.

So, you can just select a cell then run macro using Ctrl+A.
Instead of RowNum = 5, you can change the line to RowNum = ActiveCell.Row. It will give the value of the row as variable.
 
Upvote 0
No worries we all learn as we go, atleast you tried.

In regards to your question you have a few ways you could achieve this. What method you use will be up to you

Based on what you mentioned I would say you can possibly add a button to your worksheet and assign the macro you have to this button through its right click options. You select the row you want and then press the button to trigger the macro.

You could also transfer your code to the worksheet and trigger it to run based on either selection change (so when you select a row) or value change like when you change a value in a cell.

Target would be the selected cell in this example

VBA Code: 
RowNum = ActiveCell.Row

VBA Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    RowNum = Target.Row
        'Custom Brands
        If Cells(RowNum, 56).Value <> "FALSE" Then
            Cells(RowNum, 6).EntireColumn.Hidden = True
            Cells(RowNum, 7).EntireColumn.Hidden = True
            Cells(RowNum, 8).EntireColumn.Hidden = True
            Cells(RowNum, 9).EntireColumn.Hidden = True
        End If
        If Cells(RowNum, 56).Value = 1 Then
            Cells(RowNum, 6).EntireColumn.Hidden = False
            Cells(RowNum, 7).EntireColumn.Hidden = False
            Cells(RowNum, 8).EntireColumn.Hidden = False
            Cells(RowNum, 9).EntireColumn.Hidden = False
        End If


1652663269700.png
 
Upvote 0
Zot said:
You just need to RowNum value for macro execution only, right?

You can create shortcut to run your macro like Ctrl+A etc.

So, you can just select a cell then run macro using Ctrl+A.
Instead of RowNum = 5, you can change the line to RowNum = ActiveCell.Row. It will give the value of the row as variable.
Click to expand...

Ah yes I always forget you can bind them to keyboard shortcuts too :)

I would not recommend using Ctrl+A however as that is naturally select all but yeah.
 
Upvote 0
I do not understant why you need to loop through rows from row 5
You code said: If cells(5,56) <> "FALSE" then hide column 5+6+7+8
loop to next row: If cells(6,56) <> "FALSE" then hide column 5+6+7+8
and so on to last row, i.e, cells(10,56)...
It means column 5+6+7+8 is hidden or not finally depend on last row: cells(10,56)
Why not to let code work on last row only:
If cells(10,56) <> "FALSE" then hide column 5+6+7+8
 
Upvote 0
bebo021999 said:
I do not understant why you need to loop through rows from row 5
You code said: If cells(5,56) <> "FALSE" then hide column 5+6+7+8
loop to next row: If cells(6,56) <> "FALSE" then hide column 5+6+7+8
and so on to last row, i.e, cells(10,56)...
It means column 5+6+7+8 is hidden or not finally depend on last row: cells(10,56)
Why not to let code work on last row only:
If cells(10,56) <> "FALSE" then hide column 5+6+7+8
Click to expand...

From what I can see the OP does not loop as you mention and I dont see anywhere in their code that they mention If Cells(6,56) <> "FALSE"

Everything is listed as Cells(RowNum, ##) for example Cells(RowNum, 56) <> "FALSE"

He is not "looping" nor does it appear he is trying to but rather is looking to hide columns 5 + 6 + 7 + 8 when Cells(RowNum, 56) <> "FALSE" or Cells(RowNum, 56) = 1

Then also looks at additional columns/criteria

Column 57
Cells(RowNum, 57).Value <> "FALSE"

Etc.

He just wants it to trigger based on the active row vs hardcoding it to row 5
 
Upvote 0
The increments in your code seem to be of a regular size, therefore your code could be shortened considerably. Try the following.

VBA Code: 
Option Explicit
Sub Hide_Columns()
    Dim RowNum As Long: RowNum = ActiveCell.Row
    Dim ColNo As Long, CheckNo As Long, StartCol As Long, EndCol As Long
    ColNo = 56
    CheckNo = 1
    StartCol = 6
    EndCol = 9
    
    For ColNo = 56 To 67
        If Cells(RowNum, ColNo).Value <> "FALSE" Then Range(Cells(1, StartCol), Cells(1, EndCol)).EntireColumn.Hidden = True
        If Cells(RowNum, ColNo).Value = CheckNo Then Range(Cells(1, StartCol), Cells(1, EndCol)).EntireColumn.Hidden = False
        
        CheckNo = CheckNo + 1
        StartCol = StartCol + 4
        EndCol = EndCol + 4
        ColNo = ColNo + 1
    Next

End Sub
 
Upvote 0
kevin9999 said:
The increments in your code seem to be of a regular size, therefore your code could be shortened considerably. Try the following.

VBA Code: 
Option Explicit
Sub Hide_Columns()
    Dim RowNum As Long: RowNum = ActiveCell.Row
    Dim ColNo As Long, CheckNo As Long, StartCol As Long, EndCol As Long
    ColNo = 56
    CheckNo = 1
    StartCol = 6
    EndCol = 9
  
    For ColNo = 56 To 67
        If Cells(RowNum, ColNo).Value <> "FALSE" Then Range(Cells(1, StartCol), Cells(1, EndCol)).EntireColumn.Hidden = True
        If Cells(RowNum, ColNo).Value = CheckNo Then Range(Cells(1, StartCol), Cells(1, EndCol)).EntireColumn.Hidden = False
      
        CheckNo = CheckNo + 1
        StartCol = StartCol + 4
        EndCol = EndCol + 4
        ColNo = ColNo + 1
    Next

End Sub
Click to expand...
Well there is a loop :) , well done!
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Force cursor to go to specific cell in other worksheet
Replies
7
Views
298
bptaw
B
S
  • Question
Double click erase some cell content
Replies
3
Views
540
rlv01
R
T
  • Question
PDF Printing is slow
Replies
3
Views
713
Trondo
T
G
  • Solved
Loop to create charts automatically
Replies
3
Views
823
Jon Peltier
J
A
  • Solved
how to protect sheets vba code
2
Replies
19
Views
784
Scuba94
S

Forum statistics

Threads
1,214,572
Messages
6,120,306
Members
448,955
Latest member
Dreamz high

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