Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Code to go to end of current data rather than to a specific row
|
|
|
The UK's most regular Training information: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Code to go to end of current data rather than to a specific row
Resolved · High Priority · Version 2003
Reply Wed 4 Aug 2010, 12:37Delegate Samantha said...
Samantha has attended:
Excel VBA Intro Intermediate course
Code to go to end of current data rather than to a specific row
I have a master worksheet that is kept on one drive and i need users to be able to view certain parts of it in a different drive. I don't want to keep updating two worksheets so have written the follwoing code, which works, however the file size is 20mb which i think is quite large and not sure why.
The master data currently stands at 1896 rows which is why i've written the code to go to row 2000, was thinking, at present, the fact that the code will still be looking at rows 1897 to 2000, even though they are blank, may be a waste and if i can amend this it may reduce the file sizxe a bit?
In row 1 on the user sheet i have just put '=master sheet a1', so the code is just copying this formulae down (currently to row 2000), could this be making it large too?
Private Sub Workbook_Open()
Application.ScreenUpdating = False
With Worksheets("Open Codes")
.Unprotect Password:="melon"
.Range("2:2000").Clear
.Range("1:1").AutoFill .Range("1:2000")
.Range("2:2000").Font.Bold = False
.Range("b2:b2000").HorizontalAlignment = xlCenter
.Range("d2:d2000").HorizontalAlignment = xlCenter
.Range("f2:f2000").HorizontalAlignment = xlCenter
.Range("h2:h2000").HorizontalAlignment = xlCenter
.Range("j2:j2000").HorizontalAlignment = xlCenter
.Range("1:1").Select
Selection.AutoFilter
.Range("a1").Select
Selection.AutoFilter Field:=1, Criteria1:="Open"
Columns("A:AW").EntireColumn.AutoFit
Columns("m:aw").Hidden = True
Range("a1").Select
.Protect Password:="melon", AllowFiltering:=True
End With
End Sub
For upcoming training course dates see: Pricing & availability
RE: code to go to end of current data rather than to a specific
Hi Samantha
Thanks for your question
I can see nothing in the code that is making your file size particularly large.
How many worksheets are there in the file, and typically how many rows? Does the file contain many charts, and/or other drawing objects?
Thanks
Stephen
Reply Fri 13 Aug 2010, 15:14Delegate Samantha said...
RE: code to go to end of current data rather than to a specific
Hi Stephen
That's the thing that's weird - it's only one worksheet, no charts or drawing objects - the actual source file is huge too but thats because it's directly linked to lots of different workbooks.
Typically there is about the 1800 number of rows and columns got to AW.
|