Short scroll bar

JoeExcelHelp
5StarLounger
Posts: 1146
Joined: 22 Jul 2013, 18:29

Short scroll bar

Post by JoeExcelHelp »

I'll try and expleain this the best way possible

When i run this querey it produces the correct data and only occupies the range A1:E20000
However; my scroll bar reduces in size as of data occupied the entire sheet
I dont see any data but the sheet is behaving as if data exists

Thank You
Sub import_Actual_R36()
Dim strSQL As String
Dim cnn As Object
Dim rst As Object
Dim wsh As Worksheet
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Set wsh = Worksheets("HoursR36")
Worksheets("HoursR36").Range("U2:AC50000").ClearContents
'Pull records by station and system
strSQL = "select cost_center_id, cost_center_name, metric, month, value " & _
"from xxxxx " & _
"where month >= '" & Format(Worksheets("datadates").Range("B6").Value, "yyyy-mm-dd") & "' " & _
"and cost_center_id not in (3206,3103,3104,3004,3005) " & _
"and metric like '%HRS%'"

Set cnn = xxxx
Worksheets("HoursR36").Range("U2").CopyFromRecordset rst
rst.Close
Set rst = Nothing
cnn.Close
Set cnn = Nothing

With wsh.Range("Z2:Z50000")
.FormulaR1C1 = "=IF(RC22="""","""",left(RC22,3))"
.Value = .Value
End With
With wsh.Range("AA2:AA50000")
.FormulaR1C1 = "=If(RC22="""","""",IF(IsNumber(Search(""Airport"",RC22)),""AO"",IF(IsNumber(Search(""Ground"",RC22)),""GO"",""Both"")))"
.Value = .Value
End With
With wsh.Range("AB2:AB50000")
.FormulaR1C1 = "=If(RC23="""","""",IF(IsNumber(Search(""Base"",RC23)),""Base"",""OT""))"
.Value = .Value
End With

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

User avatar
HansV
Administrator
Posts: 72591
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Short scroll bar

Post by HansV »

The code enters formulas in rows 2 to 50,000, so even if the query returns only 20,000 records, the used range will extend to row 50,000.
I don't see anything in this code that would extend the used range to the bottom of the sheet (row 1,048,576).
Regards,
Hans