Adjusting an Excel Column Width Script Topic is solved

Get help with using AutoHotkey (v1.1 and older) and its commands and hotkeys
Whazzzzzup15
Posts: 94
Joined: 13 Apr 2019, 16:12

Adjusting an Excel Column Width Script

20 Sep 2021, 09:29

I have a script that adjust the width of the selected columns and ignores the hidden columns, however, if I was to selected too many rows by accident or the multiple columns, the script will cycle through each and every row creating a loop that will freeze.

Is there anyway to modify this script to only adjust the columns within checking every cell? (e.g. if I have A1:F15 selected, it will adjust only columns A through F if they are not hidden. The script currently does this, but it will cycle through each cell from A1 to F15 instead of each column)

Code: Select all

	+Right::
		If WinActive("ahk_class XLMAIN")
		{
				oExcel := ComObjActive("Excel.Application")
				;ComObjActive("Excel.Application").Cells.Columns.AutoFit ;AutoFit all columns
				for c in oExcel.selection
					if !(c.EntireColumn.Hidden)
						c.EntireColumn.AutoFit
			}
			return
		}
		else
			Send {Shift down}{End}{shift up}
		return
User avatar
mikeyww
Posts: 26437
Joined: 09 Sep 2014, 18:38

Re: Adjusting an Excel Column Width Script

20 Sep 2021, 09:33

Perhaps you can identify the selected range first, see which columns are selected, and then just adjust one cell in each of those columns.
Whazzzzzup15
Posts: 94
Joined: 13 Apr 2019, 16:12

Re: Adjusting an Excel Column Width Script

20 Sep 2021, 09:44

mikeyww wrote:
20 Sep 2021, 09:33
Perhaps you can identify the selected range first, see which columns are selected, and then just adjust one cell in each of those columns.
Am I on the right track?

Code: Select all

    +Right::

        If WinActive("ahk_class XLMAIN")
        {
                oExcel := ComObjActive("Excel.Application")
                ;ComObjActive("Excel.Application").Cells.Columns.AutoFit ;AutoFit all columns

                Dim myCol As Column
                Dim mySelRange as Range
                mySelRange = oExcel.Selection

                For each myCol in mySelRange.Columns
                    if !(c.EntireColumn.Hidden)
                        c.EntireColumn.AutoFit
            }
            return
        }
User avatar
mikeyww
Posts: 26437
Joined: 09 Sep 2014, 18:38

Re: Adjusting an Excel Column Width Script

20 Sep 2021, 10:48

Code: Select all

oExcel := ComObjActive("Excel.Application")
#IfWinActive ahk_class XLMAIN
+Right::
SoundBeep, 1500
Loop, % (sel := oExcel.Selection).Columns.Count
 If sel.Cells(cell := sel.Column + A_Index - 1).EntireColumn.Hidden
  sel.Cells(cell).EntireColumn.AutoFit
Return
#IfWinActive
User avatar
flyingDman
Posts: 2776
Joined: 29 Sep 2013, 19:01

Re: Adjusting an Excel Column Width Script

20 Sep 2021, 14:05

Code: Select all

for col in xl.selection.columns
	if !col.EntireColumn.Hidden
		col.EntireColumn.AutoFit
14.3 & 1.3.7
User avatar
FanaticGuru
Posts: 1905
Joined: 30 Sep 2013, 22:25

Re: Adjusting an Excel Column Width Script  Topic is solved

20 Sep 2021, 14:10

Whazzzzzup15 wrote:
20 Sep 2021, 09:29
Is there anyway to modify this script to only adjust the columns within checking every cell? (e.g. if I have A1:F15 selected, it will adjust only columns A through F if they are not hidden.

Looping through Columns is pretty easy as is looping through most things in Excel with COM. Generally you don't have to resort to Loop. Generally for is easier.

Code: Select all

xlApp := ComObjActive("Excel.Application")
for Column in xlApp.Selection.Columns
	if !Column.Hidden
		Column.AutoFit

FG
Hotkey Help - Help Dialog for Currently Running AHK Scripts
AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon
Hotstring Manager - Create and Manage Hotstrings
[Class] WinHook - Create Window Shell Hooks and Window Event Hooks
User avatar
mikeyww
Posts: 26437
Joined: 09 Sep 2014, 18:38

Re: Adjusting an Excel Column Width Script

20 Sep 2021, 14:12

Looks better! Thank you, FanaticGuru & flyingDman. :thumbup:
User avatar
FanaticGuru
Posts: 1905
Joined: 30 Sep 2013, 22:25

Re: Adjusting an Excel Column Width Script

20 Sep 2021, 14:15

flyingDman wrote:
20 Sep 2021, 14:05

Code: Select all

for col in xl.selection.columns
	if !col.EntireColumn.Hidden
		col.EntireColumn.AutoFit

Cross post of very similar code but EntireColumn is redundant when used on a column so is not really needed.

FG
Hotkey Help - Help Dialog for Currently Running AHK Scripts
AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon
Hotstring Manager - Create and Manage Hotstrings
[Class] WinHook - Create Window Shell Hooks and Window Event Hooks
User avatar
flyingDman
Posts: 2776
Joined: 29 Sep 2013, 19:01

Re: Adjusting an Excel Column Width Script

20 Sep 2021, 14:18

@FanaticGuru point taken!
14.3 & 1.3.7
Whazzzzzup15
Posts: 94
Joined: 13 Apr 2019, 16:12

Re: Adjusting an Excel Column Width Script

20 Sep 2021, 19:15

Thank you everyone.
Last edited by Whazzzzzup15 on 20 Sep 2021, 22:30, edited 1 time in total.
User avatar
flyingDman
Posts: 2776
Joined: 29 Sep 2013, 19:01

Re: Adjusting an Excel Column Width Script

20 Sep 2021, 21:03

If you prefer to use EntireColumn you should have chosen the code I proposed! ;). @FanaticGuru explained that it was not necessary and shorten the code a bit. FG deserves the "Topic Solved" label.
14.3 & 1.3.7

Return to “Ask for Help (v1)”

Who is online

Users browsing this forum: inseption86, just me, Rohwedder and 174 guests