Forum Discussion
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 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!
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 SubPlease 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
9 Replies
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 SubPlease 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
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,
I've adjusted the code to fit with your worksheet.
Please find the attached file.
Regards,
Haytham
Haytham Amairah thank you so much, greatly appreciated!
