Jun 02 2019 07:07 PM
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!
Jun 02 2019 09:19 PM
Solution
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
Jun 02 2019 10:49 PM
@Haytham Amairah thank you so much, greatly appreciated!
Jun 03 2019 12:03 AM
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,
Jun 03 2019 12:22 AM
I've adjusted the code to fit with your worksheet.
Please find the attached file.
Regards,
Haytham
Jun 03 2019 04:10 AM
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?
Jun 03 2019 05:45 AM
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.
Jun 03 2019 05:59 AM - edited Jun 03 2019 06:00 AM
Jun 03 2019 03:50 PM
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
Jun 04 2019 05:32 AM
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
Jun 02 2019 09:19 PM
Solution
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