Forum Discussion

leena2270's avatar
leena2270
Copper Contributor
Jun 03, 2019
Solved

Changing no. of rows in table based on value in drop down menu

Hello,  I have been looking for a way to change the no. of rows in the table dependent on the no. of factors selected (see attached image). I.e. if you select 4 from the drop down bar, I want anothe...
excel
Excel on Mac
Formulas and Functions
  • Haytham Amairah's avatar
    Haytham Amairah
    Jun 03, 2019

    leena2270

     

    Hi,

     

    You need to VBA code to do that!

     

    I suggest using this one:

    Private Sub Worksheet_Change(ByVal Target As Range)
     If Target.Address(False, False) = "B1" Then
       Dim no As Integer, counter As Integer
       no = Range("B1").Value

       If no = 0 Then Exit Sub

       Cells.Find(What:="factor name", After:=ActiveCell, LookIn:=xlFormulas, _
       LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
       MatchCase:=False, SearchFormat:=False).Activate

       ActiveCell.End(xlDown).Range("A1:D1").Select
       Selection.Copy
       ActiveCell.Offset(1, 0).Range("A1:D" & no).Select
       Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
       SkipBlanks:=False, Transpose:=False
       Application.CutCopyMode = False
       ActiveCell.Select
     End If
    End Sub

     

    Please find it in the attached file.

    The code is stored in the worksheet module, to see it:

    Hover the mouse over the worksheet tab, right-click, and select View Code.

     

    Hope that helps

leena2270's avatar
leena2270
Copper Contributor
Jun 03, 2019

Hi Haytham Amairah, 

I'm still having some struggles, the page that i am talking about is titled instructions. When I open 'Visual Basic' and copy the given code, adjusting 'B1' to D5 i get errors at: 

 ActiveCell.End(xlDown).Range("A1:D1").Select
   Selection.Copy
   ActiveCell.Offset(1, 0).Range("A1:D" & no). 

 even when I change A1:D1 to A5:D5. My apologies to trouble you again, but if you do have time, do you have any suggestions on how to fix this? 

Kind regards, 

leena2270 

  • leena2270's avatar
    leena2270
    Copper Contributor
    Jun 03, 2019

    Hi, Haytham Amairah I am back again with another question.

    I wanted to remove the first three rows because they were just example factors for the customer to put in. But if i remove the three rows and paste the same code a bug error comes up. I have also tried to find a code to clear the first cells but that hasn't worked either. Could you explain to me how you select the the range (A1:D1) and the ActiveCell.Offset range? 

    • Haytham Amairah's avatar
      Haytham Amairah
      Silver Contributor
      Jun 03, 2019

      leena2270

       

      I've fixed the code to handle that errors.

      I've also added a button to clear all the factors to start over.

      Please note that you have to keep the workbook saved in (.xlsm) extension in order to keep the codes stored in it.

       

      With regards to ActiveCell.Offset, the Offset is one of ActiveCell properties, we can use it to move away from the active cell in a certain number of rows and columns.

       

      While Range("A1:D1").Select is to tell the VBA compiler to select a range relative to the active cell but make the selected range starting from the active cell in the same size as if you start from cell A1 to cell D1.

       

      I hope that makes sense.

      • leena2270's avatar
        leena2270
        Copper Contributor
        Jun 03, 2019

        HiHaytham Amairah,

         

        Is it correct to assume that neither the ActiveCell.Offset or the range determine the placement of the rows in the table? 

        I thank you very much for your time and patience to explain this to me, and for your help. I really appreciate it 

Resources