I have a file that is linked to a folder and unites the tables into one central table
- I set the desired row height, but the problem is that clicking refresh resets the row height to a very minimal height
, how can you set that the refresh will not affect the row height?
Thank you very much for the answer!!
Leah
Hi Leah,
Make sure table is set to preserve cell formatting, after you set the table data range to Wrap Text if you want to wrap it.
however, manual row heights are never preserved, Wrap text will be the way to go, unless you used vba code to set the row height to a specific value.
The command you showed in the screenshot was already marked in my Excel,
but it didn't work. The refresh changes the height of the lines even when there is text scrolling.
I would love to receive a VBA code that knows how to keep the text scrolling in the title
and a constant line height even when refreshing,
thank you!!! Leah
Hi Leah,
The code should look like this:
Sub RefreshTable()
Dim tbl as listobject
Set tbl=ThisWorkbook.Worksheets("SheetName").ListObjects("TableName")
'refresh the table
tbl.QueryTable.Refresh BackgroundQuery:=False
'set height to data range only, not to header. Use tbl.Range instead of tbl.DataBodyRange to include header row.
if not tbl.DataBodyRange is Nothing then
tbl.DataBodyRange.EntireRow.Height = 30
End if
End Sub
Thanks!!
I put the code in the VBE editor
I came to the query - I pressed the fresh right button - and the height of the rows became small.
How do you activate the attached code?
Is it supposed to work when you press the right button - refresh??
You have to adjust the code to your file. Replace in code SheetName and TableName with your real values, I lost my crystal ball to see these names in your file. Adjust also the height you want, i used 30, but have a strange feeling I did not guess the value you have in mind 🙂
Then, go to excel ribbon, insert any shape. Right click the shape>Assign Macro>pick the RefreshTable macro.
To refresh the query, you have to click this button/shape, not from excel Refresh menu.
Hope it helps, let me know please if you managed to make it work.
Cheers,
Catalin
With your permission, I expand the question,
In the table I need to refresh - its first columns receive data from folders with Power Query
and in the second part I added a lot of complex formulas.
Now I need protection with a password user will not be able to access the wording
so I set up partial protection but then when I run the macro a DEBUG error comes up.
It is important to emphasize that the first part of the table that retrieves the data in the query
is not password protected
But the columns with the formulas after it that are defined
in a dynamic table - the cells are locked, so I can't refresh.
what can we do??
Thank you!!!
Before refresh, unprotect the sheet:
tbl.Parent.Unprotect Password:="Your password here"
tbl.QueryTable.Refresh BackgroundQuery:=False
Bewfore End Sub, protect the sheet back:
tbl.Parent.Protect Password:="Your password here"
Hi,
If Catalin solution does not help, which is unlikely, here is a solution on row height from ChatGPT.
It sounds like you're using Power Query in Excel to consolidate tables from multiple files into one central table, and you're encountering an issue with row height resetting when you refresh the data.
Row height is generally controlled in the Excel worksheet rather than in Power Query. Here's a solution to help maintain your desired row height when refreshing data:
-
Adjust Row Height in Excel:
- Before using Power Query to load data, make sure the rows are set to your desired height in the Excel worksheet. To do this, select the rows you want to adjust, right-click, choose "Row Height," and set the desired height.
-
Freeze Row Height in Excel:
- After setting the row height, you can "freeze" it so that it remains the same even when the data is refreshed. To do this, you can lock the rows by selecting the row below your desired height, and then go to the "View" tab and click on "Freeze Panes." This will keep your row heights intact.
-
Refresh Data:
- When you refresh your data in Power Query, the row heights in the Excel worksheet should not be affected if you have frozen the panes correctly.
