Need to hide cells based on Inputs and outputs

mikechavarria

New Member
Joined
Apr 25, 2011
Messages
3
Can you help? I am creating a form that will create an excel sheet with a specified number of inputs which are the rows and outputs which are the columns but I can not seem to figure out how to make the inputs count be a selection of column A and the amount of rows needed. I also need to do this with the columns but this is to be done via inputs from a form.


Top is device selection, middle is the inputs and third is the outputs.
o3zv0.png



This is the code to use for the selection but how do I apply coordinates for x, y from this form?
Code:
Sub HideRowsAndColumns()
Dim row1 As Long, row2 As Long
Dim col1 As Long, col2 As Long
If TypeName(Selection) <> "Range" Then Exit Sub
'If last row or last column is hidden, unhide all and quit
If Rows(Rows.Count).EntireRow.Hidden Or _
    Columns(Columns.Count).EntireColumn.Hidden Then
    Cells.EntireColumn.Hidden = False
    Cells.EntireRow.Hidden = False
    Exit Sub
   End If
 
    row1 = Selection.Rows(1).Row
    row2 = row1 + Selection.Rows.Count - 1
    col1 = Selection.Columns(1).Column
    col2 = col + Selection.Columns.Count - 1
 
    Application.ScreenUpdating = False
    On Error Resume Next
    'Hide Rows
    Range(Cells(1, 1), Cells(row1 - 1, 1)).EntireRow.Hidden = True
    Range(Cells(row2 + 1, 1), Cells(Rows.Count, 1)).EntireRow = True
    'Hide Columns
    Range(Cells(1, 1), Cells(1, col1 - 1)).EntireColumn.Hidden = True
    Range(Cells(1, col2 + 1), Cells(1, Columns.Count)).EntireColumn = True
 
End Sub
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Forum statistics

Threads
1,224,585
Messages
6,179,696
Members
452,938
Latest member
babeneker

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