how can you reduce column width and row height by a percentage

leecavturbo

Well-known Member
Joined
Jan 4, 2008
Messages
622
rows and columns are not the same through out the sheet so need to keep aspect but reduce all?
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

goesr

Well-known Member
Joined
Nov 15, 2013
Messages
662
Hi leecavturbo - The code below reduces the width of column A by 50%. It can easily be modified to whatever percentage you want and RowHeight can be similarly modified. Let me know if you need further assistance.

Code:
Sub leecavturbo_Column_Width()
    With ActiveSheet.Columns("A")
        .ColumnWidth = .ColumnWidth * 0.5
    End With
End Sub
 

Teeroy

Well-known Member
Joined
Nov 9, 2012
Messages
2,244
Office Version
  1. 365
Platform
  1. Windows
You can easily do it with VBA.

Code:
Sub ScaleRowAndColSizes()
Dim snglScalefactor As Single, Rw, Col
snglScalefactor= .75

For Each Rw In ActiveSheet.UsedRange.Rows
    Rw.RowHeight = Rw.Height * snglScalefactor
Next
For Each Col In ActiveSheet.UsedRange.Columns
    Col.ColumnWidth = Col.ColumnWidth * snglScalefactor
Next
End Sub
 
Last edited:

leecavturbo

Well-known Member
Joined
Jan 4, 2008
Messages
622

ADVERTISEMENT

Thx for the rapid reply.
What i didn't realise is that won't reduce cell content sizes too. Font
! ?
 

Teeroy

Well-known Member
Joined
Nov 9, 2012
Messages
2,244
Office Version
  1. 365
Platform
  1. Windows
@leecavturbo, I don't understand your comment. Are you in fact asking to change the zoom level rather than the row and column sizes?
 

Teeroy

Well-known Member
Joined
Nov 9, 2012
Messages
2,244
Office Version
  1. 365
Platform
  1. Windows
Teeroy - Very nice solution! :)
Thanks goesr, I find a simple solution is usually the best. Especially if I have to try and understand it 2 weeks later :LOL:.
 

leecavturbo

Well-known Member
Joined
Jan 4, 2008
Messages
622
@leecavturbo, I don't understand your comment. Are you in fact asking to change the zoom level rather than the row and column sizes?

I need the sheet to be at 100% zoom when it fills my screen.
Zoom doesn't cover why
 

Watch MrExcel Video

Forum statistics

Threads
1,130,307
Messages
5,641,436
Members
417,209
Latest member
Agbarker

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