DocAElstein
08-23-2014, 02:28 AM
<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> TestEvaluateVBA2_n1n2n3()<br>Range("H3:H4") = Evaluate("**" & Range("B3:B4").Address & "** " & "&""****""&" & "**** " & Range("C3:C4").Address & "" & "&""****""&" & "" & Range("D3:D4").Address & "")<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> TestEvaluateVBA2_n1n2n3()<br>Range("H3:H4") = Evaluate("**" & Range("B3:B4").Address & "** " & "&""****""&" & "**** " & Range("C3:C4").Address & "" & "&""****""&" & "" & Range("D3:D4").Address & "")<br><SPAN
[Code]Sub VBAOnErrorResumeNext()
On Error Resume Next
Dim TNominator As Long, RslTwat As Long
' Other Code
Let TNominator = 0
Let RslTwat = 10 / TNominator ' This will error because of an attempt to divide by zero
MsgBox Err.Number & " " & Err.Description ' This does give infomation despite that the exception has been cleared.. wierd and not as one might have expected.
' other code
Dim Rng As Range
Let Rng.Value = "Anyfink" ' This line should error as we have not assigned any object to rng. ( We cannot therefore asssing a Value to a non existing range
MsgBox Err.Number & " " & Err.Description
' 0ther code
End Sub
rinkymehra
How to delete worksheets without Loop.
Rajan Verma has posted the following on 03-04-2014 03:15 AM:
After a long time I came up with an interesting trick, it might be rarely useful for anyone but it is tricky. And the trick is "How to delete all worksheets except one" without any loops by VBA. I always love to use array everywhere (I mean where possibleJ), so here is the trick Worksheets() […]
How to delete worksheets without Loop. http://excelpoweruser.wordpress.com/2014/03/03/how-to-delete-worksheets-without-loop/
http://www.excelfox.com/forum/f13/delete-worksheets-without-loop-515/
Hi guys
This VBA trick provides a clever solution for deleting all worksheets except one without using loops. The use of arrays showcases an efficient approach.
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxUbeYSvsBH2Gianox4AaABAg. 9VYH-07VTyW9gJV5fDAZNe
https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxLtKj969oiIu7zNb94AaABAg
https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgyhQ73u0C3V4bEPhYB4AaABAg
https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgzIElpI5OFExnUyrk14AaABAg. 9fsvd9zwZii9gMUka-NbIZ
https://www.youtube.com/watch?v=jdPeMPT98QU
https://www.youtube.com/watch?v=QdwDnUz96W0&lc=Ugx3syV3Bw6bxddVyBx4AaABAg
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHAfree fire name
Last edited by Aliyaa; 07-28-2023 at 04:49 PM.
Hello Aliyaa,
Well, there are some clever ideas here, but they may be just half the story. That is to say it may be just half as clever as it looks at first glance.
We are doing multiple things without looping.
There are some ideas which I personally call Range Evaluate things, for want of a better or more formal title. These involve doing things in some neat ways without loping, things which one might more commonly and traditionally do in classical looping coding. Some of our cleverest members, like Rick Rothstein and snb , pioneered some of these ideas. I personally take an interest in these ideas as well.
But coming back to the subject of this old thread.
The first half is to do with an alternative option we have to deleting worksheets in a classic loop. It is nothing to do with any of our clever ideas, but without it we could not do the whole thing in a neat compact non looping way.
For example.
This would be a classic way to delete the second, third and forth worksheets with a loop
Code:
' https://www.excelfox.com/forum/showthread.php/1719-How-to-delete-worksheets-without-Loop?p=21151&viewfull=1#post21151
Sub ClassicLoop()
Dim Cnt As Long
For Cnt = 2 To 4
ThisWorkbook.Worksheets.Item(Cnt).Delete
Next Cnt
End Sub
Now, Microsoft strangely let us access worksheets with an array, rather than the more simple and obvious single worksheet references above. They let us do this as an alternative
Code:
Sub MicrosoftLetUsDoItInOneGoFuckKnowsWhy()
ThisWorkbook.Worksheets.Item(Array(2, 3, 4)).Delete
End Sub
Fuck knows why Microsoft let us do that. For all we know, they may have some classic looping coding to do that, like a function of some kind, that recognises when we give them an array, and then rewrites coding in the more classic looping form.
So there is nothing clever from us users there, and for all we know there might be nothing clever there at all.
The second half of the story in this thread is to make that array of numbers, Array(2, 3, 4) , without looping in a neat compact form. That uses some of the clever ideas from some of us users, of the Range Evaluate things type techniques.
Alan
Hi guys
Deleting worksheets without using a loop in Excel can be achieved by following a straightforward approach. First, select the worksheets you want to delete, hold down the Shift key, right-click on the selected worksheets, and choose "Delete.ffstylishname.com-free fire name
Quote Originally Posted by Aliyaa View Post
..Deleting worksheets without using a loop in Excel ... achieved by following a straightforward approach. First, select the worksheets you want to delete, hold down the Shift key, right-click on the selected worksheets, ...
I had never tried that sort of thing before. I just did. There is a bit more to it, - some interesting things, and also some things to be aware of.. (You also have not quite explained it correctly**)
_ If you run a macro recording whilst doing that sort of thing, then we see given a code line taking this general form:
Sheets(Array("Sheet2", "Sheet3", "Sheet4")).Select
So I guess that is telling us that manually with this way of doing it we are doing the equivalent of using that strangely allowed way to access worksheets with an array
_ **If you use this sort of thing, you should first select one of the sheets you want to select , and then hold the Shift key and select the others you want to select. (Don’t hold the shift key down and then start selecting the sheets, or otherwise you may get one extra sheet selected, that you did not want – that will be the sheet selected at the time you first held the shift key down
(**if you do this … First, select the worksheets you want to delete, hold down the Shift key……… then you will just get the last sheet selected that you selected , )
_ There appears to be some “quirk”. We might call it a bug, whereas Microsoft would likely initially at least, call it a feature. This sort of thing only seems to let you select consecutive sheets. What I mean by that is , try, for example to select the second and forth worksheet tab. So far in all the Excel versions that I have tried it wont let me do that. If I try then I also get the third worksheet selected.
That is weird, because these sort of code lines do seem to work to select the second and forth worksheet only
Sheets(Array(2, 4)).Select
Sheets(Array("Sheet2", "Sheet4")).Select
Alan
HomInspect.org
<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> TestEvaluateVBA2_n1n2n3()<br>Range("H3:H4") = Evaluate("**" & Range("B3:B4").Address & "** " & "&""****""&" & "**** " & Range("C3:C4").Address & "" & "&""****""&" & "" & Range("D3:D4").Address & "")<br><SPAN
[Code]Sub VBAOnErrorResumeNext()
On Error Resume Next
Dim TNominator As Long, RslTwat As Long
' Other Code
Let TNominator = 0
Let RslTwat = 10 / TNominator ' This will error because of an attempt to divide by zero
MsgBox Err.Number & " " & Err.Description ' This does give infomation despite that the exception has been cleared.. wierd and not as one might have expected.
' other code
Dim Rng As Range
Let Rng.Value = "Anyfink" ' This line should error as we have not assigned any object to rng. ( We cannot therefore asssing a Value to a non existing range
MsgBox Err.Number & " " & Err.Description
' 0ther code
End Sub
rinkymehra
How to delete worksheets without Loop.
Rajan Verma has posted the following on 03-04-2014 03:15 AM:
After a long time I came up with an interesting trick, it might be rarely useful for anyone but it is tricky. And the trick is "How to delete all worksheets except one" without any loops by VBA. I always love to use array everywhere (I mean where possibleJ), so here is the trick Worksheets() […]
How to delete worksheets without Loop. http://excelpoweruser.wordpress.com/2014/03/03/how-to-delete-worksheets-without-loop/
http://www.excelfox.com/forum/f13/delete-worksheets-without-loop-515/
Hi guys
This VBA trick provides a clever solution for deleting all worksheets except one without using loops. The use of arrays showcases an efficient approach.
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxUbeYSvsBH2Gianox4AaABAg. 9VYH-07VTyW9gJV5fDAZNe
https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxLtKj969oiIu7zNb94AaABAg
https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgyhQ73u0C3V4bEPhYB4AaABAg
https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgzIElpI5OFExnUyrk14AaABAg. 9fsvd9zwZii9gMUka-NbIZ
https://www.youtube.com/watch?v=jdPeMPT98QU
https://www.youtube.com/watch?v=QdwDnUz96W0&lc=Ugx3syV3Bw6bxddVyBx4AaABAg
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHAfree fire name
Last edited by Aliyaa; 07-28-2023 at 04:49 PM.
Hello Aliyaa,
Well, there are some clever ideas here, but they may be just half the story. That is to say it may be just half as clever as it looks at first glance.
We are doing multiple things without looping.
There are some ideas which I personally call Range Evaluate things, for want of a better or more formal title. These involve doing things in some neat ways without loping, things which one might more commonly and traditionally do in classical looping coding. Some of our cleverest members, like Rick Rothstein and snb , pioneered some of these ideas. I personally take an interest in these ideas as well.
But coming back to the subject of this old thread.
The first half is to do with an alternative option we have to deleting worksheets in a classic loop. It is nothing to do with any of our clever ideas, but without it we could not do the whole thing in a neat compact non looping way.
For example.
This would be a classic way to delete the second, third and forth worksheets with a loop
Code:
' https://www.excelfox.com/forum/showthread.php/1719-How-to-delete-worksheets-without-Loop?p=21151&viewfull=1#post21151
Sub ClassicLoop()
Dim Cnt As Long
For Cnt = 2 To 4
ThisWorkbook.Worksheets.Item(Cnt).Delete
Next Cnt
End Sub
Now, Microsoft strangely let us access worksheets with an array, rather than the more simple and obvious single worksheet references above. They let us do this as an alternative
Code:
Sub MicrosoftLetUsDoItInOneGoFuckKnowsWhy()
ThisWorkbook.Worksheets.Item(Array(2, 3, 4)).Delete
End Sub
Fuck knows why Microsoft let us do that. For all we know, they may have some classic looping coding to do that, like a function of some kind, that recognises when we give them an array, and then rewrites coding in the more classic looping form.
So there is nothing clever from us users there, and for all we know there might be nothing clever there at all.
The second half of the story in this thread is to make that array of numbers, Array(2, 3, 4) , without looping in a neat compact form. That uses some of the clever ideas from some of us users, of the Range Evaluate things type techniques.
Alan
Hi guys
Deleting worksheets without using a loop in Excel can be achieved by following a straightforward approach. First, select the worksheets you want to delete, hold down the Shift key, right-click on the selected worksheets, and choose "Delete.ffstylishname.com-free fire name
Quote Originally Posted by Aliyaa View Post
..Deleting worksheets without using a loop in Excel ... achieved by following a straightforward approach. First, select the worksheets you want to delete, hold down the Shift key, right-click on the selected worksheets, ...
I had never tried that sort of thing before. I just did. There is a bit more to it, - some interesting things, and also some things to be aware of.. (You also have not quite explained it correctly**)
_ If you run a macro recording whilst doing that sort of thing, then we see given a code line taking this general form:
Sheets(Array("Sheet2", "Sheet3", "Sheet4")).Select
So I guess that is telling us that manually with this way of doing it we are doing the equivalent of using that strangely allowed way to access worksheets with an array
_ **If you use this sort of thing, you should first select one of the sheets you want to select , and then hold the Shift key and select the others you want to select. (Don’t hold the shift key down and then start selecting the sheets, or otherwise you may get one extra sheet selected, that you did not want – that will be the sheet selected at the time you first held the shift key down
(**if you do this … First, select the worksheets you want to delete, hold down the Shift key……… then you will just get the last sheet selected that you selected , )
_ There appears to be some “quirk”. We might call it a bug, whereas Microsoft would likely initially at least, call it a feature. This sort of thing only seems to let you select consecutive sheets. What I mean by that is , try, for example to select the second and forth worksheet tab. So far in all the Excel versions that I have tried it wont let me do that. If I try then I also get the third worksheet selected.
That is weird, because these sort of code lines do seem to work to select the second and forth worksheet only
Sheets(Array(2, 4)).Select
Sheets(Array("Sheet2", "Sheet4")).Select
Alan
HomInspect.org