VBA Excel RowHeight (Diff-Range)

imfarhan

Board Regular
Joined
Jan 29, 2010
Messages
106
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

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

imfarhan

Board Regular
Joined
Jan 29, 2010
Messages
106
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
76,263
Office Version
  1. 365
Platform
  1. 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
106

ADVERTISEMENT

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
106

ADVERTISEMENT

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
76,263
Office Version
  1. 365
Platform
  1. 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
76,263
Office Version
  1. 365
Platform
  1. Windows
Bob

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

Watch MrExcel Video

Forum statistics

Threads
1,127,063
Messages
5,622,470
Members
415,897
Latest member
Barry18180

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