Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 549
  • Last Modified:

Hiding Rows via VBA

Rows 4 to 34 represent 1 row per day in a month.  In doing testing on how to hide the rows I have run into a strange situation.

My hiding code:

Dim idxRow as Double
For idxRow = 4 To 34 Step 1
    Rows(idxRow & "." & idxRow).Select
    Selection.EntireRow.Hidden = True
Next

What happens is row 4 gets highlighted and goes away,  rows 5 to 34 move up, at the point of highlighting row 5 the shading jumps to row 6 and row 6 goes away, rows 7 to 34 move up and all continues normally.

If I manually hide rows 4 to 34 then run the above code with a False option row 5 does not unhide.

I have deleted row 5 and copy/inserted row 8 into 5th place.  Procedure still fails on row 5.

I am trying this because I will eventually be selectively hiding rows, not the whole range.

Ideas on why row 5 won't hide/unhide?  I assume there is something about row 5.
0
IBMJunkman
Asked:
IBMJunkman
  • 12
  • 8
  • 4
  • +3
1 Solution
 
StephenJRCommented:
Do it backwards, loop from 34 to 4 step -1.
0
 
StephenJRCommented:
Excel gets confused when deleting or hiding and misses out rows, as you have found out.
0
 
dlmilleCommented:
Another alternative (for the back pocket) is to create a range for all ranges to act on, then act once - more efficient, and gives you a chance to "checK' what ranges would be hidden or deleted, or whatever, as you could select it, pop  it up, or show it in a debug window.

Dim idxRow as Double
dim hideRng as range

For idxRow = 4 To 34 Step 1
    if hiderng is nothing then
       set hiderng =     Rows(idxRow & "." & idxRow)
    else
       set hiderng = union(hiderng,rows(idxrow & "." & idxrow)
    end if
Next

if not hiderng is nothing then hiderng.entirerow.hidden = true
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
dlmilleCommented:
I missed a parenthesis:


Dim idxRow As Double
Dim hideRng As Range

For idxRow = 4 To 34 Step 1
    If hideRng Is Nothing Then
       Set hideRng = Rows(idxRow & "." & idxRow)
    Else
       Set hideRng = Union(hideRng, Rows(idxRow & "." & idxRow))
    End If
Next

If Not hideRng Is Nothing Then hideRng.EntireRow.Hidden = True

Open in new window

0
 
IBMJunkmanAuthor Commented:
Still have problems.  I created a brand new sheet.  Put data in rows 1 - 22.  Added a button to hide 4 - 20 and one to unhide 4 - 20.  Row 5 does not hide.  If I manually hide row 5 it will not unhide using code.

To hide row 5 with code I have to hard code  Rows(4.6).Select then hide selection.  And the unhide loop does not unhide row 5.

Excel has a problem with row 5!



0
 
dlmilleCommented:
what code are you running? As you've gotten a couple suggestions, so hard to tell from where we sit.

I've never used code like Rows(4.6), so I assume its from 4 to 6, correct?  My syntax has always been Range("4:6") so just might be worth confirming.

Did you run my code, or modify yours based on prior input?

Dave
0
 
IBMJunkmanAuthor Commented:
OK, got it working.  Surprised Excel was not complaining.  And no idea why the behaviour was what it was.

Final working code:

Dim idxRow As Double
For idxRow = 4 To 34 Step 1
    Rows(idxRow & ":" & idxRow).Select
    Selection.EntireRow.Hidden = True
Next

Note the semi-colon.  I originally had a period.   :-)

I am learning various bits of code to see if I can do what I want to do in the final workbook.

0
 
dlmilleCommented:
Whew - Not sure what the period does, never used it before.  Glad that helped suss out the problem.

Actually Rows(4.7).Select appears to round up to Row(5) - so you were definately not getting the desired result using period!


PS - no need for select - rarely needed and it impacts efficiency:
 
   Rows(idxRow & ":" & idxRow).Hidden = True 'this should suffice.  Rows already affects the entire row

Cheers,

Dave
0
 
IBMJunkmanAuthor Commented:
Thanks.  It is faster without the Select first.

Are there any books that explain this stuff?  On Amazon I see John Walkenbach's  Excel 2010 Power Programming with VBA and
VBA and Macros: Microsoft Excel 2010 from Bill Jelen.

The Microsoft Excel 2010 Inside out is useless.

0
 
dlmilleCommented:
Suggest NEVER activating or selecting ANYTHING, unless the function you're using requires it.  Can't think of any top of mind, but there are a few and it becomes evident you need to at that point, anyway.

I just bought Walkenbach's Excel bible (to catch up from a manual perspective on all the great functions, lol - my last was 97 and thanks to E-E I've been catching up, but wanted a reference that I could read through).  I've never bought a VBA book - Walkenbach has come highly recommended.  I'll get an E-E expert who teaches the stuff to post his recommendation in this thread.

Cheers,

Dave
0
 
byundtCommented:
One of the biggest exceptions regarding avoiding cell selection when writing VBA code: With Excel 2003 and earlier, conditional formatting using relative addressing in a "formula is" criteria involved a formula with references relative to the active cell rather than the one being formatted. The formula in such cases would be far too confusing (and hence prone to error) if you didn't select the cell first.

Brad
0
 
rspahitzCommented:
I used to recommend the Teach Yourself Excel in 21 Days, but it seems that the last published version of that was 2000!
I need to start looking for some new books (or maybe write my own updated version of that.)

FYI
VBA is a strange beast.  Other programming languages besides VB, the developers seem to like focusing on the best way to talk to the computer (i.e. cryptic).  In the VB world, most people focus on getting the job done but are often forced to get a bit cryptic because many of the components you interact with were created by C++ developers who don't understand the concept of simplicity.

and the VBA world is even more obscure (now) because its focus is one specific component (such as Excel or Word as an application.)  In many cases, these components were written using the old techniques that worked well when there were a limited number of functions / methods available; with so many more now, it gets difficult to find things so we rely on the same old techniques we used to use even if there are far better ones now.  Anyway, when working with VB (aka VBA for Windows) you are exposed to many more features and often learn new techniques because of that.  In the Office VBA world, it seem much harder to find the new features as the appear.

Anyway, I'll check around for some new books and see if I find any that look worth reviewing.

FYI
One of my students suggest that I create a workbook for my classes.  Great idea, until I realized just how much work it is to set up exercises to test the many features of any particular application or feature.
If anyone has ideas, that would also work well in book format.
0
 
broro183Commented:
hi everyone,

This is a post full of links, but... why should we re-write what others have already put so eloquently (or not, as the case may be!)?

IBMJunkMan,

I second Dave's suggestion of avoiding the use of ".select" or ".activate" where-ever possible. You can think of it in terms of "dot processing" which Walkenbach mentions...

After an object is assigned to a variable, VBA can access it more quickly than it can
a normal lengthy reference that has to be resolved. So when speed is critical, use
object variables. One way to think about this is in terms of “dot processing.” Every
time VBA encounters a dot, as in Sheets(1).Range(“A1”), it takes time to
resolve the reference. Using an object variable reduces the number of dots to be
processed. The fewer the dots, the faster the processing time. Another way to
improve the speed of your code is by using the With-End With construct, which
also reduces the number of dots to be processed.
Sourced from: p197 of Walkenbach's "Excel 2002 Power Programming with VBA".

I think you have found a good book if you pick up any of Walkenbach's "Power Programming..." series. I devoured the "2002 Formulas" & the "2002 Power Programming..." books and I suspect that the newer versions have continued to build on the older edition of the books. Another book that has good reviews but that I haven't yet purchased is "Professional Excel Development - Second Edition" ( http://www.appspro.com/Books/ProExcelDev.htm ). This may be slightly more advanced than you need at this time because "It starts where other Excel books leave off and builds on the techniques you already know to demonstrate how professional Excel programmers create commercial quality Excel applications.".

Books are good but you'll also learn heaps just by googling key phrases (often, including "sub" in your query will help find solutions as well as questions), searching & asking questions on sites such as http://www.experts-exchange.com or www.theCodeCage.com etc, or checking out any of the Excel MVP's sites which are listed at the below link: http://www.mvps.org/links.html#Excel

The next link is a big list of links ranging from Dave McRitchie's "getting started with macros" to a great variety of excel topics... http://www.excelforum.com/excel-general/620254-excel-useful-links-see-last-post-for-new-additions.html
Or these threads ( http://www.excelforum.com/the-water-cooler/744969-dos-and-donts-so-others-wont.html & http://www.excelforum.com/excel-programming/718395-what-to-avoid-in-vba.html ) which discusses a lot of excel related vba principles. Note that these are just people's thoughts and it is worth reading the whole thread to get a full range of opinions (the regulars on E-E may even recognise contributions from a few fellow E-E experts).


StephenJR,
I agree wholeheartedly that when deleting rows, the looping should be done from bottom to top. However, I've never seen "Excel get confused when ... hiding and misses out rows". So I'm curious, what circumstances caused problems with hiding rows from "top to bottom"?
Actually, one example where excel did seem to get confused, was when there were linked ActiveX buttons on the same sheet that the rows were deleted from (some buttons in rows above & some buttons in the rows below). This issue caused me a fair bit of trouble before I found a reference to what (I think!?) was the cause, "Graphical objects may not shift correctly when you delete rows or cells above them.", which is mentioned in: http://office-watch.com/t/n.aspx?a=849 


Brad,
My normal comment for the exceptions where ".select" is needed is for setting the location of "activewindow.freezepanes".  I had never considered the relevance of selecting the appropriate cell to assist with relative addressing of conditional formatting - I'll keep it in mind for the future :-)

Rspahitz,
I'm not sure if it is the same book, but I also have "Teach yourself Excel programming with Visual Basic for Applications in 21 days" by Matthew Harris (1995 or 1996?) on my desk. I picked it up for free 2 years ago in a second hand book shop & since then I've loaned it to a couple of people who want to learn about excel. It seems to be an oldie but a goodie :-)

Dave,
I may have wandered slightly off the topic but hopefully some of my links help the OP :-)

hth
Rob
0
 
IBMJunkmanAuthor Commented:
Broro183,

Thanks for the post.  I visited my local Coinstar machine yesterday and now have a $190 certificate Amazon.  Time to buy some books!

With this current project I have I will be back here.  Glad I have a paid account!

0
 
StephenJRCommented:
Oops I didn't even spot the '.' and I must admit I was scratching my head after writing that as to why Excel would behave like that.
0
 
IBMJunkmanAuthor Commented:
I blame it on my 63 year old eyes. :-)
0
 
dlmilleCommented:
Yep - Excel doesn't even BLINK - it just rounds that number and uses it, lol ;)

Dave
0
 
StephenJRCommented:
Unless my eyes are 19 years older than the rest of me, I don't even have that excuse!
0
 
rspahitzCommented:
Regarding dots...I'm not sure that books typically go into the optimization process.  There are many things to consider.
The main thing is to minimize the amount of effort required for the processor, which requires two parts:
1) how long will it take to set it up?
2) how much time do you save?

For example,

Cells(25,10).Font.Bold = True

might take 5ms.

You could also do something like this to achieve the same thing:

Dim objCell as Range
Dim objFont as Font

Set objCell = Cells(25,10)
Set objFont = ObjCell.Font
objFont.Bold = True

For sure, this will take longer than 5ms and will take more memory.

Is it worth it? Not unless you plan to use the objCell variable for many other things or to use it in a loop (which is re-using it many times anyway.)

The general rule for optimization is to "localize" anything that will be used over and over.

Another overlooked example is this, to put something into every 5th row of a sheet:

For iRow = 1 to 10
  For iColumn = 1 to 20
    Cells(iRow * 5, iColumn).Value = iRow * iColumn +5
  Next iColumn
Next iRow

This is inefficient because Excel has to calculate iRow*5 every time iColumn changes, and yet that iRow*5 gives the exact same results each time in that inner loop, which requires more processor time.
To optimize this, remove the redundant calculation outside the loop:


For iRow = 1 to 10
  iTempRow = iRow * 5
  For iColumn = 1 to 20
    Cells(iTempRow, iColumn).Value = iRow * iColumn +5
  Next iColumn
Next iRow

since a processor fetch or memory is much faster than a calculation, you save time this way.
So why not do the same with "iRow * iColumn +5"?  The answer is that anything you did to try to optimize that would make it worse.  You can't put that outside the iColumn loop because it requires the iColumn counter to determine the result; you can't rework it in any way.

If you had something like this:

iRow * iColumn + iRow + 5

then you could pull out the iRow+5 portion to reduce one additional calculation:

iRowPlus5 = iRow + 5
...
iRow * iColumn + iRowPlus5

Similarly, if you had (iColumn +1) * iRow , this can be fixed, although it's also less obvious, and may not be much faster:

(iColumn +3) * iRow => iColumn * iRow + 3 * iRow =>

iRowTimes3 = 3 * iRow
...
iColumn * iRow + iRowTimes3

--
There are so many "moving parts" when it comes to optimization that I think it would be difficult to write a good book on it.
0
 
broro183Commented:
I'm pleased I could add something even if it was slightly off the original topic.
Good luck with your learning :-)

__________________
Rob Brockett.
A Kiwi in the UK.
Always learning & the best way to learn is to experience...
0
 
dlmilleCommented:
Rob -

I thought we were discussing Rows(4.7) versus Rows(4:7) - the former really being a "type-o"

:)

Dave
0
 
rspahitzCommented:
Dave....I guess I got lost in the discussion of books and optimizing things with "." in them....but Row 4.7 could certainly confuse Excel too!
--
RobS
0
 
dlmilleCommented:
You're telling me ;)
0
 
broro183Commented:
yep, I guess I got lost in the book discussions too!

Too many Robs eh?

Rob Brockett
0
 
IBMJunkmanAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for IBMJunkman's comment http:/Q_27419498.html#37058057

for the following reason:

My type was the problem
0
 
rspahitzCommented:
darn...all these quality opinions and strategies need to be moved to a new location....
0
 
dlmilleCommented:
I IBMJunkman - That wasn't the only problem.  recall the syntax using "." rather than ":" for the range definition on row selection

Agreed?

Dave
0
 
dlmilleCommented:
IBMJunkman - you can also ACCEPT your own solution as the solution - that way this question can stay in the knowledgebase as a reference.

Cheers,

Dave
0
 
IBMJunkmanAuthor Commented:
My typo was the problem.  Really appreciate all the other code info and book info.
0
 
dlmilleCommented:
IBMJunkman - so what WAS your type-o?

:)
0
 
IBMJunkmanAuthor Commented:
I typed  Rows(idxRow & "." & idxRow).Select which resulted in  Rows(4.4).Select


It should be  Rows(idxRow & ":" & idxRow).Select which resulted in  Rows(4:4).Select

Period vs Colon

The latter is correct.  Excel did not complain about the 4.4  
0
 
dlmilleCommented:
Great - I thought I did when I pointed that out in http:/Q_27419498.html#a37045108 - you might recall we spent a few posts on that and complained about our eyes ;)

Glad you got it working.

Cheers,

Dave
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 12
  • 8
  • 4
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now