Excel Rows and Columns Hiding with VBA

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.
This is the code to use for the selection but how do I apply coordinates for x, y from this form?

Private Sub cmdbtnSetMatrix_Click()
Dim txtBox1 As String

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Test Matrix")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
  .End(xlUp).Offset(1, 0).Row

'check for a Device Name, Inputs count and Outputs count
If Trim(Me.comboBoxMan.Value) = "" Then
  Me.comboBoxMan.SetFocus
  MsgBox "Please select a Manufacture"
  Exit Sub
End If

If Trim(Me.comboBoxMan.Value) = "Select a Manufacture" Then
Me.comboBoxMan.SetFocus
MsgBox "You must select a manufacture to move on!"
Exit Sub
End If

'If Trim(Me.txtboxDeviceName.Value) = "" Then
' Me.txtboxDeviceName.SetFocus
  'MsgBox "Please enter a Device name"
  'Exit Sub
'End If
  
  If Trim(Me.txtBoxInputs.Value) = "" Then
  Me.txtBoxInputs.SetFocus
  MsgBox "Please enter the amount of inputs the device has"
  Exit Sub
End If
  
  If Trim(Me.txtBoxOutputs.Value) = "" Then
  Me.txtBoxOutputs.SetFocus
  MsgBox "Please enter the amount of outputs the device has"
  Exit Sub
  
  End If
  
  If Trim(Me.comboBoxMan.Value) = "Select a control signal type" Then
  Me.comboBoxMan.SetFocus
  MsgBox "You must select a control signal type before continuing"
  Exit Sub
  
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.comboBoxMan.Value
'ws.Cells(iRow, 2).Value = Me.txtboxDeviceName.Value
ws.Cells(iRow, 3).Value = Me.txtBoxInputs.Value
ws.Cells(iRow, 4).Value = Me.txtBoxOutputs.Value
'ws.Cells(iRow, 5).Value = Me.ComboBox1.Value

'clear the data
'Me.txtboxDeviceName.Value = ""
Me.txtBoxInputs.Value = ""
Me.txtBoxOutputs.Value = ""
'Me.ComboBox1.ListIndex = 0
Me.comboBoxMan.ListIndex = 0
'Me.txtboxDeviceName.SetFocus
End Sub

I have no idea what you said. Why don't explain what you want to happen given a particular set of values entered into those textboxes on the form?

Middle box is device inputs that I want to be rows in column A, so what ever amount is entered in the middle box that is how many rows that need to be selected.

Bottom box is the amount of device outputs, This should select the amount of columns needed in the sheet.

The code you see will hide everything not selected.

So essentially I am selecting Rows and Columns and then hiding what I don't need

@cbae:cpachYou Never Know Till You Try!

@cpach:

Hi, I am not sure what you are trying to do. But, do you know there is a simple way to hide the rows? It is called Filter (Normally available in Home Tab by default). You can easily create a column with increasing numbers, and you can filter by saying showing only rows with values larger than specific number. Or you simply give a column as Categories/Tags and you can filter by Categories and such.

This is a better way because you didn't really hide the rows. Anyone can look at whole data by simply disabling Filter. They don't need weird code to unhide rows.

Once you can do this manually. Simply record the Macro of what you did manually, and modify its code for your liking.

Hope this help, even though it is not exactly what you want. As for columns, you have to think of something else.

@cpach:

Also I recommend creating new sheet, and copy the columns you desired instead of modifying the source. Since it is copy/paste approach, you don't have to select all the columns at once, you can do it one column at a time easily. Personally I don't like to funky hidden rows/columns in my data sheet. The result sheet can be easily created and copied with the columns you like and your source is still untouched.

Not really answering your question, just saying there are other ways to do it that you no longer need to address the original problem you have.

How to select ranges. Also works on Rows and Columns (with some differences):

http://support.microsoft.com/kb/291308

And Selection has a boolean Hidden property. Set it = True to hide.

But I too would keep the original data in one sheet (in the same file) and either generate a new sheet from the inputs or create a second sheet that displays the subset of data via lookup formulas. It depends on the nature of the data and the intended use for the resulting sheet.