VBA Excel RowHeight (Diff-Range)

imfarhan

Board Regular
Joined
Jan 29, 2010
Messages
101
Hi All,
I have got MS Query written by 7 sheets of Excel (Sun to Sat)
each day have got different values comes ..some time 2 rows some time 6rows

My following VBA code is working fine , only problem is the row height I restricted to 1: to 6 as you can see in the code given below:
I would like to change the row size to 53.4 ONLY where the value exist not specific rage 1:6
Problem in red fonts area given below:-

Code:
   'Check total waiters workbook is open select if available, exit if not
    'For Each wbk In wbReport
        If InStr(UCase(swb.Name), "Weekly C Floor Report.xls") <> 0 Then
            For Each wSheet In swb.Worksheets
                wks = wSheet.Name
''''''   START OF CODE
                       Sheets(wks).Select
                       ActiveSheet.PageSetup.Orientation = xlLandscape
                       With ActiveSheet.PageSetup
                           .LeftMargin = Excel.Application.InchesToPoints(0.2)
                           .RightMargin = Excel.Application.InchesToPoints(0.2)
                           .FitToPagesWide = 1
                           .FitToPagesTall = 3
                           .Zoom = 85
                           .CenterHeader = "&A"
                           Columns("C:C").ColumnWidth = 10.11
                           Columns("D:D").ColumnWidth = 20.67
                           Columns("F:F").ColumnWidth = 20.67
                           Columns("H:H").ColumnWidth = 11.78
                           'Columns("N:N").ColumnWidth = 10.78
                           Columns("G:G").ColumnWidth = 31.78
 
 
                          [COLOR=darkred] [B]Rows("1:6").Select[/B][/COLOR]
[B][COLOR=darkred]                              Selection.RowHeight = 53.4[/COLOR][/B]
                       End With
Many thanks
F
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

imfarhan

Board Regular
Joined
Jan 29, 2010
Messages
101
All, I would like to know , change the rowsheight for only those rows where the value exists.

Many thanks.. waiting for your reply plz
F
 

Phildaburn

Board Regular
Joined
Feb 4, 2011
Messages
146
Set up a variable, "row" for example. Assign it a value of 1, then using a do while loop, increment your row until you hit a value of "". You final entry then would be row = row - 1 (to make row equal to the last row with a value), then use your variable in your select to change the row height.

Dim row as integer
dim col as integer
row = 1
col = 1
do while cells(rw, col).Value <> ""
row = row + 1
loop

row = row - 1
 
Last edited:

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,459
Office Version
365
Platform
Windows
Try something like this.
Code:
With wks

    With .PageSetup
                .Orientation = xlLandscape
                .LeftMargin = Excel.Application.InchesToPoints(0.2)
                .RightMargin = Excel.Application.InchesToPoints(0.2)
                .FitToPagesWide = 1
                .FitToPagesTall = 3
                .Zoom = 85
                .CenterHeader = "&A"
     End With

     .Columns("C:C").ColumnWidth = 10.11
     .Columns("D:D").ColumnWidth = 20.67
     .Columns("F:F").ColumnWidth = 20.67
     .Columns("H:H").ColumnWidth = 11.78
     '.Columns("N:N").ColumnWidth = 10.78
     .Columns("G:G").ColumnWidth = 31.78
 
     Set rng = .Range("A2") ' assumes data starts in row 2 column A
                
     ' loop through rows and set the row height if there is data                               
     While rng.Value <> ""
                
           If rng.Value <> "" Then
                  rng.EntireRow.RowHeight = 53.4
           End If
 
           Set rng = rng.Offset(1)

     Wend
 
End With
I hope you don't mind but I changed some of the other code, there were some things in it that could possibly cause problems.:)
 

imfarhan

Board Regular
Joined
Jan 29, 2010
Messages
101
Found the solution,

..........code...
Columns("H:H").ColumnWidth = 11.78
'Columns("N:N").ColumnWidth = 10.78
Columns("G:G").ColumnWidth = 31.78
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.RowHeight = 53.4
End With

Thanks every one
Farhan
 

boblarson

MrExcel MVP
Joined
Nov 14, 2008
Messages
1,964
Shouldn't this thread be in the Excel Category and not in Microsoft Access? It sounds like it is all happening from within Excel and Access is just the data source.
 

imfarhan

Board Regular
Joined
Jan 29, 2010
Messages
101
Hi Boblarson,
I think you're right it should be in Ms Exel, I think its because I 'm controlling all VBA code through Ms Access not Ms Excel thats why I put this in Ms Access rather than.

Meaning, In excel the data coming from Ms-Query using Ms Access tables and so on.

But to understanding for the forum , Ithink you're right it should be in Ms-Excel I will be careful next time. Thanks for point out.
Many thanks
Farhan
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,459
Office Version
365
Platform
Windows
Farhan

Do you mean your code in Access uses MS Query to export data to Excel?

Why don't you use the built-in methods in Access like TransferSpreadsheet to export the data?
 

boblarson

MrExcel MVP
Joined
Nov 14, 2008
Messages
1,964
Hi Boblarson,
I think you're right it should be in Ms Exel, I think its because I 'm controlling all VBA code through Ms Access not Ms Excel thats why I put this in Ms Access rather than.

Meaning, In excel the data coming from Ms-Query using Ms Access tables and so on.

But to understanding for the forum , Ithink you're right it should be in Ms-Excel I will be careful next time. Thanks for point out.
Many thanks
Farhan

If you are using Access for the code then your code is going to cause an unintended instance of Excel to open because your code isn't tied to an explicit Excel application object. I am posting from my cell phone or else I would post a link to my quick tutorial on that. If you go to my website and then to Quick Tutorials and then to All Versions there is a link at the bottom of the list which is titled something like "Why Excel..."
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,459
Office Version
365
Platform
Windows
Bob

Are you referring to the use of things like ActiveSheet, Columns etc without references?
 

Forum statistics

Threads
1,089,267
Messages
5,407,276
Members
403,131
Latest member
Lewas2019

This Week's Hot Topics

Top