Macro Help to Locate Last Row of Data and Open another workbook from users desktop

I'm needing some help with writing a macro to perform to the following tasks.
1) Open another workbook from the users desktop. The name of the other workbook is
test.xlsx and will always be located on the users desktop. When the macro completes
its task, then the test.xlsx file will be renamed to test(rev).xlsx and also reside on the
users desktop.

2) Located the last row of data in the test.xlsx file. When this integer value is known
I need format a range of data, such as drawing borders, changing fonts, etc....

The range will "always" begin on ROW 11 (A11:Z11) .... Columns A to Z
but the Last Row is dependent upon the test.xlsx file
Essentially I am looking for this macro to define the range of (A11:Zxx) .. xx is the last row of data

Thank you in advance for any help.
Cheers

Assuming that the sheet on workbook test is called Sheet1 and the longest column is column A try...

Please Login or Register  to view this content.

I am just about to go to work and so haven't put in the change name code but I am sure someone will jump in.

Try

Please Login or Register  to view this content.

Edit: Please note that cross-posting without a link is against the forum rules
http://www.mrexcel.com/forum/excel-q...ine-range.html

http://www.excelforum.com/forum-rule...rum-rules.html

Originally Posted by WasWodge

Assuming that the sheet on workbook test is called Sheet1 and the longest column is column A try...

How many rows does column A have? Change the red A to the column letter of your longest column.

Please Login or Register  to view this content.

Something like below. Use the macro recorder to get any other formatting you need. It will fit in the syntax below.
Remember to change the A and also please note that in future I will not take part in any threads where you cross-post without providing links.

Please Login or Register  to view this content.

or untested this should do the same without the select

Please Login or Register  to view this content.

The 2nd "untested" version is much shorter/condensed. I tested and it
works perfect. btw.. The opening routine to get the file from the desktop
is working fine. I'm still hardcoding the File Closing Routine with the changed
name of Budget.xlsx as I haven't had any luck with the syntax. Any suggestions
are appreciated. Thanks

I discovered (trial & error ..ha) the code for saving and changing the file name so please disregard the
previous post because all is good.

The last routine in my macro I'm trying to code is embedding the autosum function to sum
an entire column of numbers.

I have 23 columns of data. All columns are same length, 1st Column is always G and
the Last Column is always AC First row of data is always found in Row 12
Last row of data is obviously variable. When the columns have the autosum function then
the final step is to sum the Last Row from G to AC and place the tabulated value in Cell L8

Your code found the last row in the previous steps for drawing the
borders and works great but ..... I can't seem to make the necessary
coding change to make this simple column addition work.
Any suggestions are greatly appreciated. Thanks.

you should really have started a new thread as really it is a different question but anyway try the code below.
Rather than sum the total of the columns it just sums the entire range(change the sheetname to suit). Remove the red -1 if you don't have column totals.

Please Login or Register  to view this content.

I didn't think about a new thread but certainly agree the topic is changed. I will repost this one in a new post/thread.
btw.. The code does work exactly as you described and thank you.

It's likely my lack of narrative description but not exactly what I'm trying to accomplish. The goal is to have a "live" formula embedded
into cell L8. And also a "live" formula embedded on the last row of columns G to AC summing each column.

The reason this is important for my particular project is because the end user will update data on the newly created spreadsheet.
As the new data entry's are made the live formulas will always update with the correct totals. I really appreciate your expertise
with the project. Thank you again for your kind assistance with my project

Do you have a spare column? If yes what is it?

Try the code below. Change every "AD" to your empty column letter

Please Login or Register  to view this content.

I tried the code. The vacant column AD will work fine. My test data file has 68 rows. The code displayed the column totals and formula on Row 69. The data file the macro opens has the hardcoded sum value at the end of each column. Therefore your code should over write that hard coded total. If the code moves the formula up just one row I think it accomplishes this task.

As your code is written it places the formula and range total of all columns in Cell L8 perfectly.

There are a few other locations that need to display the Column Total and Formula to make this routine complete.
Cell L2 should display the formula and value of Column Total Gxx ... xx is sum of column G
Cell L3 should display the formula and value of Column Total Wxx ... xx is sum of column W
Cell L4 should display the formula and value of Column Total Hxx ... xx is sum of column H
Cell L5 should display the formula and value of Column Total Ixx ... xx is sum of column I
Cell L6 should display the formula and value of Column Total Jxx ... xx is sum of column J

As an example: Cell L2 the excel formula I reference would be =Gxx xx would be the last row number of Column G

If I can get the above working this project is complete ! Your code is really compact and I can't say I understand the syntax as it seems really advanced. For disclosure I did post this topic into a new post/thread. I wasn't sure from your previous post if
that is what you intended. Thank you again for your expertise. btw.. you have the best quote I've read so far !!

I am at work now so I won't be able to look at it until tonight but having said that rather than adjusting the ranges it is a lot easier just to clear the row with the totals first.
The other bits will have to wait till tonight.

First of all to answer your question in the previous post.

Does this same hold true for the way the code will work.

The formulas are the same as if you put them in by hand its just they are put in with code. This being the case they exhibit the same behavior and so the simple answer is yes.
Try the code below but remember it has been written to run when there is a totals row, if you run it without the totals then you will lose a row of data (and because you are doing it with code you can't undo the action).

Please Login or Register  to view this content.

All I can say is wow !! Code works perfect. I really thought this would be many more lines of code.
I understand and yes the data file will always have a numeric value in the column totals so no
problem there.

I notice the code does wipe out the number format of the column totals (last row)
They should display two decimals with a comma example.. xx,xxx.xx
Instead the values display no comma xxxxx.x or xxxxx or xxxxx.xx depending on the values significant digits
This occurs only on the last row. The values copied into the other bits keep there format.

Is there a line of code to format the last row values (after the clear statement & code above executes ) to display
the values with 2 decimal places, use comma, right aligned & vertical center, Font is Calibri 9 ? If so, this completes
the project. Again many thanks. I'm a few years behind your expertise.

Record yourself doing it manually with the macro recorder and then post the code it gives you back in the thread.

I'll shorten it etc. a bit sometime tonight

I think this covers everything

Please Login or Register  to view this content.

or possibly just

Please Login or Register  to view this content.