Autofilter numerous columns/fields in Excel

Hi All,

Am new here, so please forgive any in-house standards I don’t follow. I’m trying to write an applescript that will create an autofilter for 3 columns (columns G, H, and I). I then want a dialog box to pop up that asks the user to enter some details that they want to filter. For example, column H is a column that contains the name of an author. So the dialog box pops up saying “first author, if known”. The user then hits enter, and 2 more dialog boxes show, each in turn, asking the user to put in more information.

Important: The user may not know the value of all 3 fields, but may know only one (the first author without knowing the publisher, for example). In this instance, the user should be able to leave the dialog blank or something equivalent in a way that means Excel won’t try to use that blank information as something to search for (i.e. it shouldn’t start looking for a blank cell if the dialog box is left blank).

I’ve posted my scripting so far below using only 2 columns. If you run this, you’ll find that autofilters are set up for columns G and H, but that only the most recently entered data into a dialog box (range G:G in the script below) is filtered for, and all previous filters (range H:H) are not filtered.

Because I’m only able to filter one column using the current script, I’m unable to test whether leaving a dialog box blank will result in a filter for all blank cells, or will not try to filter that column.

tell application "Microsoft Excel"
	
	-- clear any filters
	show all data active sheet
	set autofilter mode of active sheet to false
	
	tell range "G:H" of active sheet
		--single cell, applies to current region
		autofilter range
		-- with no parameters, toggles dropdowns
		set s to display dialog "first author if known" default answer ""
		
		autofilter range field 1 criteria1 text returned of s operator ¬
			autofilter with visible drop down
		
	end tell
	
	tell range "G:G" of active sheet
		--single cell, applies to current region
		autofilter range
		-- with no parameters, toggles dropdowns
		set refx to display dialog "in-text citation if known" default answer ""
		
		autofilter range field 1 criteria1 text returned of refx operator ¬
			autofilter with visible drop down
		
	end tell
end tell

I have lifted this code from the internet from various places, apologies for the lack of acknowledgement