VBA for Automated DropDown Functions

dijohnguitar

New Member
Joined
Jan 13, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
I have a spreadsheet that has Columns A-L. Dropdown lists are located in Columns F-K. All of my DropDowns have a default value of "(Select Value)" which obviously instructs users to pick a value from the dropdown. I'm trying to see if it's possible to do the following:
- In Column K, this dropdown selection will insert a new blank row under the current row, and remove all contents and validation EXCEPT in Column K. However, if they select "None" or if the Dropdown stays with "(Select Value)" no new row will be created.
- The Dropdown from Column K needs to copy down to the new row into Column K and be set with the "(Select Value)" title
- I need this to repeat for every row of data on the sheet

The information for the data validation in Column K comes from a separate sheet entitled "SOFTWARE" which has a substantial amount of choices (A1:A182). I'd certainly appreciate any assistance on this. I've set up a Macro to run exactly what I'm asking above, but it's only doing it for 1 row and not copying down for all the rows. I'd really appreciate the Excel Gurus with helping me tackle this one. Thank you!!

Here's the VBA that I currently have:

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("K3")) Is Nothing Then
        Select Case Range("K3")
            Case Is <> "(select value)": Macro1
           
        End Select
    End If
    End Sub


And the MACRO I have set up:

VBA Code:
Sub Macro1()
'
' Macro1 Macro
'
'
Rows("4:4").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("K3").Select
Selection.AutoFill Destination:=Range("K3:K4"), Type:=xlFillDefault
Range("K3:K4").Select
Range("A4:J4").Select
Selection.ClearContents
Selection.Validation.Delete
Range("K4").Select
Range("K4") = "(select value)"

End Sub
 
Last edited by a moderator:

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

fadee2

Active Member
Joined
Nov 7, 2020
Messages
336
Office Version
  1. 2019
Platform
  1. Windows
hi,
If I understand it correctly, try the following and see if it works.

VBA Code:
Sub Macro1()
'
' Macro1 Macro
'
lastrow = Cells(Rows.Count, 1).End(xlUp).Row

Rows(lastrow).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
'Range("K3").Select             <<<<<<< not required
Range("k3").AutoFill Destination:=Range("K3:K" & lastrow), Type:=xlFillDefault
'Range("K3:K4").Select          <<<<<<< not required
Range("A" & lastrow, "J" & lastrow).ClearContents
Range("A" & lastrow, "J" & lastrow).Validation.Delete
'Selection.Validation.Delete    <<<<<<< not required
'Range("K" & lastrow).Select    <<<<<<< not required
Range("K" & lastrow) = "(select value)"

End Sub


hth...
 

dijohnguitar

New Member
Joined
Jan 13, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hello Fadee2,

The code you provided did not work. What it ended up doing was this: as soon as I select an option from the dropdown in Column K, it copied and pasted the dropdown from the first row, to the 2nd-to-last row. It then inserted a blank row after that with the dropdown menu. The new dropdown on the inserted row did not insert any new rows after it once a selection was made. I really appreciate you helping me with this.
 

fadee2

Active Member
Joined
Nov 7, 2020
Messages
336
Office Version
  1. 2019
Platform
  1. Windows
Well I only updated your code so that it would work for every row.
anyway, can you share your part of your worksheet in question?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,589
Messages
5,625,671
Members
416,125
Latest member
NeedExcelHelp2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top