Skip Navigation

Coffeehouse Thread

7 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

Excel Rows and Columns Hiding with VBA

Back to Forum: Coffeehouse
  • User profile image
    cpach

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

    Code:

    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

     

  • User profile image
    cbae

    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?

  • User profile image
    cpach

    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

  • User profile image
    cpach

    @cbae:cpachYou Never Know Till You Try!

    quotereplyedit

    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

  • User profile image
    magicalclick

    @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.

    Leaving WM on 5/2018 if no apps, no dedicated billboards where I drive, no Store name.
    Last modified
  • User profile image
    magicalclick

    @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.

    Leaving WM on 5/2018 if no apps, no dedicated billboards where I drive, no Store name.
    Last modified
  • User profile image
    DCMonkey

    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.

     

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.