VBA Excel RowHeight (Diff-Range)

[imfarhan]

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:-

   '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

 

[imfarhan]

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]

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

 

[Norie]

Try something like this.

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]

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]

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]

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]

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]

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]

Bob

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