Macro to reset column to first item in data validation drop down list

RAS123

New Member
Joined
Jul 23, 2019
Messages
4
Hi there,

I wonder if I might find help here,

From F9:F325 I have cells with data validation '=IF(L9,DropdownRange,E9)' to '=IF(L325,DropdownRange,E325)', which works fine.

I would like to click a button to reset every cell in F9:F325 to the first (and only) item in the dropdown when the corresponding L#=False.

Any help appreciated,

Many thanks

RAS
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Hello,


Assuming your variable DropDownRange is indeed a range object:
This code loops through each cell in Column L of your range and if it is False it changes the cell in column F to the first value in your DropDownRange

Code:
Sub ResetCells()
    Dim c As Range
    
    For Each c In Range("L9:L325")
        If c = "False" Then
            Range("F" & c.Row) = DropDownRange.Cells(1, 1)
        End If
    Next c
End Sub
 
Upvote 0
Hi, I registered only today so am a complete MrExcel newbie. How can I upload the simplified worksheet i've created to illustrate the problem?
 
Upvote 0
Hi, I registered only today so am a complete MrExcel newbie. How can I upload the simplified worksheet i've created to illustrate the problem?

create account on dropbox.com (or box.com) and share the link in this post.
 
Upvote 0
Hello,


Assuming your variable DropDownRange is indeed a range object:
This code loops through each cell in Column L of your range and if it is False it changes the cell in column F to the first value in your DropDownRange

Code:
Sub ResetCells()
    Dim c As Range
    
    For Each c In Range("L9:L325")
        If c = "False" Then
            Range("F" & c.Row) = DropDownRange.Cells(1, 1)
        End If
    Next c
End Sub

Hi Gallen, thank you for trying. Perhaps this link makes the problem a bit clearer

https://www.dropbox.com/s/w9lahbh1805dt7c/Example Sheet.xlsx?dl=0

Thanks
 
Upvote 0
Hi, and welcome to Mr. Excel!
It's often that case that you want to upload a copy of just part of your worksheet (selected cells/whole table etc), as a picture really is worth a thousand words.

If you have a look here you'll see how to download & install the HTML-maker.
It's a great piece of kit, and is only an Excel add-in, so it's easy to download, install & use. It creates HTML of your selected cells (AND formulae, if you wish) and places the code onto your clipboard - for you to paste straight into your post on the forum.

All the best
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,176
Members
448,554
Latest member
Gleisner2

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
Back
Top