Excel - Adjust Vertical Scroll Bar. Solved/Closed

Question

Hello,

I'm looking to create a macro so that the vertical scrollbar (on the right side) automatically adjusts to the number of non-empty rows.

For example, I have a database with a thousand rows; by default, my vertical scrollbar is well-adjusted, meaning that when I select it and drag it all the way down, it reaches the height of my 1000th row. But if I make a mistake (like pressing "CTRL" + "Shift" + "Down Arrow" on an empty cell), I end up with a selection going down to row 65536. In this case, the vertical scrollbar is adjusted accordingly, and it's quite complicated to find my 1000th row by scrolling through the vertical scrollbar...

While searching online, I found the code: scrollbar1.max=range("cell").value but it doesn't work for me, giving the error: "Object required". Plus, nothing guarantees that it's the right code for my problem. I also found the term: "verticalscrollbar" which I think corresponds more closely to the vertical scrollbar, but how do I assign a value to it?

Does anyone have any ideas?

PS: I'm not very good at VBA, so I apologize if I've made any mistakes.

Thank you in advance.

--
If you don't know, ask; if you know, share.

Configuration: Windows XP Internet Explorer 6.0 Excel 2002 SP3

BlackPearl · Accepted Answer

Hello everyone,

With Excel 2003, there is a fairly simple technique to adjust the scrollbar on the right.
Select the row just after the end of your table and press "Ctrl+Shift+Down Arrow" to select the empty rows until the end of the existing rows in Excel, which is 65536.
Then press "Alt, E, S" in sequence (shortcut to delete). Go back to cell A1 and "Ctrl+S" (save).

Normally the scrollbar should adjust...

lermite222 · Answer

Hello,
You need to set the range, both Row and Column
Sheets("LaFeuille").ScrollArea = "a1:AA1000"
To reset the entire sheet
Sheets("LaFeuille").ScrollArea = ""
Cheers
--
Experience teaches more surely than advice. (André Gide)

stranger-46 · Answer

Hello lermite222,

Your solution doesn't really suit me. I just modified my first post with a screenshot to better visualize what I'm trying to accomplish.

Here’s the link:

http://pix.nofrag.com/8/f/0/aeafd388b9a2c53d0e357b7fda0dd.html
--
If you don't know, ask, if you know, share.

stranger-46 · Answer

Hm....I can't modify my posts (at least the first one). Too bad.

So here is a screenshot to summarize what I'm trying to do:

http://pix.nofrag.com/8/f/0/aeafd388b9a2c53d0e357b7fda0dd.html

It's a basic example.

In fact, the macro in question would help me for all the files I use, to automatically adjust the vertical bar according to the number of filled lines.

I hope this explanation is clear enough.

--
If you don't know, ask; if you do know, share.

lermite222 · Answer

But it is necessary to adjust the range to the number of rows and columns
Sheets("LaFeuille").ScrollArea = Range("A1").SpecialCells(xlCellTypeLastCell).address
--
Experience teaches more surely than advice. (André Gide)

lermite222 · Answer

Perhaps it will be better if you put the last formula in the Change event of the sheet, but to leave room for any possible additions...
Dim Col as long
Dim Lig as long
col = Range("A1").SpecialCells(xlCellTypeLastCell).column +2
Lig = Range("A1").SpecialCells(xlCellTypeLastCell).Rows + 2
Me.ScrollArea = Range(Cells(1,1),Cells(Lig,Col))
--
Experience teaches more surely than advice. (André Gide)

eriiic · Answer

Hello,

If, starting from the last line you want to see, you hold shift+ctrl+down arrow to select the last lines and then hide them (right-click to hide), it does exactly what you want, it seems to me.

Eric

stranger-46 · Answer

Hello,

lermite222 >>> by copying your code I get the following error: "Invalid use of Me keyword"
eriiic >>> yes, I have already used this trick and while it may work for my use, it won't be the case for other users.

The problem with "scrollarea" and hiding unused lines is that it can't be used transparently for the user. For my personal use, the "scrollarea" code could be very useful. But the macro won't be intended solely for my use.

I'm looking to create a macro that, for example, upon opening a sheet, will automatically and transparently adjust the size of the VerticalScrollbar to the number of non-empty rows, while still allowing the possibility of continuing to fill in hundreds of other rows if needed.

I would have thought that the code "scrollbar1.max=range("cellule").value" could be adapted to my problem just by changing "scrollbar1" to "verticalscrollbar", if only everything were that simple then I would just need to count the number of non-empty cells and the scrollbar size would change according to that value... but that's just a dream of mine :(

Thank you for your suggestions, I will continue to search online at the same time.

--
If you don't know, ask, if you do know, share.

lermite222 · Answer

To conclude Eric's presentation,
Me. can only be placed in a sheet module. Not in a general module or ThisWorkbook.
If you place the macro from post 6 in the sheet events... It will work.
If 2 empty rows are not enough you can add +3, +5, etc.
But if you want to add a block of 100 rows for example, place the cursor on the last empty row, copy it and as the event changes will be activated, the scroll will automatically update on your 100 rows + 2.
Another possibility, if the user goes to the first empty row and enters something, automatically, there will be 2 free rows below.

Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim Col as long Dim Lig as long col = Range("A1").SpecialCells(xlCellTypeLastCell).column +2 Lig = Range("A1").SpecialCells(xlCellTypeLastCell).Rows + 2 Me.ScrollArea = Range(Cells(1,1),Cells(Lig,Col)) End Sub

I hope it's clear enough, if not you say...
See you
--
Experience teaches more surely than advice. (André Gide)

stranger-46 · Answer

Ok, thanks for all these details.

So I tried to test the code as you advised me, but I'm still getting the error: "Type mismatch" on the bold line

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Col As Long
Dim Lig As Long
Col = Range("A1").SpecialCells(xlCellTypeLastCell).Column + 2
Lig = Range("A1").SpecialCells(xlCellTypeLastCell).Rows + 2
Worksheets("Sheet1").ScrollArea = Range(Cells(1, 1), Cells(Lig, Col))
End Sub

Same for:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Col As Long
Dim Lig As Long
Col = Range("A1").SpecialCells(xlCellTypeLastCell).Column + 2
Lig = Range("A1").SpecialCells(xlCellTypeLastCell).Rows + 2
Me.ScrollArea = Range(Cells(1, 1), Cells(Lig, Col))
End Sub

Am I doing something wrong?
--
If you don’t know, ask; if you know, share.

lermite222 · Answer

Sorry, I thought it would be simple but actually it's a bit more complicated...
No need to put it in ThisWorkbook but in the sheets

Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim Col As Long, A Dim Lig As Long Application.EnableEvents = False 'When defining A, it goes in a loop... Me.ScrollArea = "" 'if not released it crashes when we define it larger. A = Range("A1").SpecialCells(xlCellTypeLastCell).Address Col = Range(A).Column + 2 Lig = Range(A).Row + 2 n'No 'S' in Row Me.ScrollArea = Range(Cells(1, 1), Cells(Lig, Col)).Address Application.EnableEvents = True End Sub

Now I tested it and it works as mentioned...
A+
--
Experience teaches more surely than advice. (André Gide)

stranger-46 · Answer

Ok, the code works this time, I thought I was really left-handed about it :)

However, there's another problem...

I'm working on a sheet that originally had 400 occupied rows, I've deleted 300.

And the macro detects the 400 rows and not the 100 filled ones, so the drilldown goes up to the 400th row instead of the 100th...

The important thing is that the code works and I've seen it in action.

Nevertheless, I don't think I'll use it. Because what I was looking for is total transparency for the user, and I know that I would already have to give the same explanations you gave me earlier (for new rows, etc.), which I want to avoid.

In any case, a big thank you to you "lermite222", your code has been useful in a way because I think I could use it for myself. Then I imagine that what I want is not always feasible.
Thanks also to you "eriiic" for your advice.

PS: I will continue my research on my side, if I find a solution I will write it here :)

See you later

--
If you don't know, ask, if you know, share.

mz soft · Answer

for the connoisseurs soft program 1-display hidden files in Excel 2-view the *.pif file adjust all the measures of your choice fix@*

Excel - Adjust Vertical Scroll Bar.

13 réponses

Windows spotlight is not working.

Masked destination layer

Samsung j5 unable to answer

Scam of the suvedene highway

Only one of my iphone headphones works. what to do?

Mb to mb conversion and difference.

I have a philips tv breakdown: the screen is black.

File name too long

No sound with ps3 and hdmi

Wake up from sleep mode in windows 10.