Duane in Japan


I recorded an actual Macro and it looks like this.

Application.CommandBars("Web").Visible = False
Range("A28").Select
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
Range("A29").Select
ActiveWindow.SmallScroll Down:=3


What I actually have is thousands of rows populated, Three rows are visible with data in the top three rows of my active sheet, 26, 27 & 28 in this example.

When I first open the spreadsheet, I need to make the WEB toolbar go away, this part is good.

I then need to click on the very last hyperlink in 'A' column, I think it may be refered to something like lastused range This example is 'A28'.

I then need to put the cursor in the next available empty cell past the lastused range in the 'A' column and post it in the very top left corner, (as though it was in cell A1 ready to work).

This empty cell was in the fourth row so I did three smallscroll to get it to the top row in view.

When I record the macro as you see above it records the actual cell number, this will not help me but I know someone here can please.

I also need the macro to run slowly giving the internet time to upload the hyperlink and then revert back to finish the macro, it just zips right through it now and does not end up in the correct cell when completed.

Thanks in advance for any help.

Lastly, is there a way to make the WEB toolbar stay gone, it wasnt in view by default prior to some MS update I am guessing




Re: VBA Excel short Macro Ending in 1st Open Cell

Duane in Japan


Done some more research and got the active cell to go to 'last cell' but I have A,B,& C columns filled and I could not get the active cell to go to A column, please help with code above as actual task but I dont need to define exact cell name as above, thanks.






Re: VBA Excel short Macro Ending in 1st Open Cell

Ridgy Didge

I think the problem you describe relates to what I want to do. So I haven't got an answer for you but wonder if you have worked out how to .

I want to go to the bottom of a selection and add data to the empty cells below it.

Suppose I have a region A1:X100. In this region is a column (M). Column "M" contains data from row 1 to row 80. However this is a dynamic region and the length of column M is also dynamic.

I want to locate the first empty cell in column M, (within the "current region" which for the moment is A1:X100). Then I want to fill all the empty cells in column M (inside "current region") with a value.

I can't find code to enable me to navigate inside a "current region". I wonder if you have any thoughts on this problem.






Re: VBA Excel short Macro Ending in 1st Open Cell

AussieJames

This might help ... have a look at the special cells function of ranges. In the example you describe where you're looking for the first empty cell in a column. Start in the first cell of the region (say it's A1) and have a look at the following:

Range("A1").End(xlDown).Row
Range("A1").SpecialCells(xlCellTypeLastCell).Row

The first will give you the row of the next blank cell in the column (you can also use xlRight etc) and the second one will find the last cell in the sheet. When using these - especially the first one - be sure to trap 2 key events: 1 - the first cell of the region is the only blank cell; and 2 - there's no data in the column (so the above = 65536). If you determine the size of the region first you can kick out of any loop when the above > MyRegionLastRow ...

If you need to populate all cells in a region then use a "for each cell in region" loop, or just count the rows and columns in the region, start from the first cell and run through a double loop of something like:

for i = 1 to countofrowsinregion

for j = 1 to countofcolumnsinregion

next j

next i

Depending on what you're doing in the region, the Range("A1").CurrentRange.AutoFill might be useful ...

Hope that helps / points you in the right direction.

J.