SOLVED

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

Copper Contributor

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 another row to appear below the 'cycle' row. I've googled for about 2 hrs and looked in conditional formatting, but i assume its an 'If' rule. If anyone knows how to do it please let me know and thanks in advance!Screen Shot 2019-06-03 at 11.45.38.png

9 Replies
best response confirmed by leena2270 (Copper Contributor)
Solution

@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 thank you so much, greatly appreciated! 

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

 

I've adjusted the code to fit with your worksheet.

Please find the attached file.

 

Regards,

Haytham

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

 

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 

 

Sorry!

It seems that there is another bug.

But I've fixed it in the attached workbook.

Hi@Haytham 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 

@leena2270 

 

If you looking at the code you will see these lines:

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

These lines will move the cursor to the starting cell and activated it which is the cell that holds the name of (Factor Name).

After we define the starting cell, we can use the other properties such as Offset to move in any direction and execute the operations that we want.

 

Hope that helps

1 best response

Accepted Solutions
best response confirmed by leena2270 (Copper Contributor)
Solution

@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

View solution in original post