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

Haytham Amairah's avatar
Haytham Amairah
Silver Contributor
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? 

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

    Haytham Amairah thank you so much, greatly appreciated! 

Resources