• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Selecting Next cell based on Range

Excel Newcomer

New Member
I have created a code to hide rows if the cell is blank. I am also trying to incorporate the code to "select" the cell that was just unhidden. This code works, but is extremely slow. Is there anything better I could be doing?

Code:
Dim r As Range, cel As Range
Set r = Range("H113:H164")
Application.ScreenUpdating = False
For Each cel In r
'These rows have text but need to be hidden until getting to that row ... I did not know how to do this which is why it is here at the moment
    Rows("125:149").EntireRow.Hidden = True
'
    If Len(cel.Text) = 0 Then
        cel.EntireRow.Hidden = True
    Else
        cel.EntireRow.Hidden = False
        cel.Select
    End If
Next cel
Application.ScreenUpdating = True


Thank you
 
Hi ,

Before writing code , always visualize the entire process , and work it out manually ; that will reveal any flaw in the logic.

1. The statement :

Rows("125:149").EntireRow.Hidden = True

is doing something ; you need to ask yourself whether it should be inside the For ... Next loop , or can it be shifted outside the loop ; clearly , shifting it outside the loop will reduce the time for execution.

2. You say you wish to select the cell which is unhidden ; does this mean that in the range H113:H164 there will be only one cell which has data in it ? Or can there be many cells which have data in them ? If so , do you want to select the last cell which had data in it ?

If there will be many cells which have data in them , then selecting the first cell which has data in it is immaterial because when execution comes to the next cell which has data , it will select this cell , thus making the earlier selection irrelevant.

3. If your only objective is to hide / unhide rows based on whether cells in column H have data in them , you can avoid the loop altogether by using the Excel AutoFilter method ; check out the help on this.

Narayan
 
Thank you for the response.

1. I took out that row, and am trying to find another place for it (broke up the range into two sections)

2. Many cells can have data in it. I want to select the cell in the range in the row that was just unhidden. The code written above works, but is slow

3. The data and actions are specific. If complete or incomplete on some rows, the next row always needs to show. But if incomplete, a different row (~50 rows apart) also needs to show
 
Hi ,

Even after your clarifications , clarity is still in doubt. You say :
I want to select the cell in the range in the row that was just unhidden.
Why ?

What purpose does this serve ?

When a program is being executed , actions follow one another in milliseconds ; what purpose will be served in selecting cells one after the other in such rapid succession ?

Secondly , you are scanning the range H113 through H164 ; the rows 125:149 fall within this range ; you say the following :
hide rows if the cell is blank.
'These rows have text but need to be hidden until getting to that row
What is the purpose of this ? You will hide these rows , the remaining code will unhide them , and then again this line will hide them.

It all sounds incomprehensible , to me.

Narayan
 
Let me rephrase. If rows 2-10 are is hidden and A1 is selected to begin (but empty). Once A1 has text, row 2 will show and A2 must be selected. A2 is currently empty. Once A2 has text, row 3 will show and A3 must be selected.

^^That is what I cannot get to happen. When A2 shows, A1 is still selected. I am trying to figure out how to always select the cell that just showed


Thank you
 
Hi ,

Sorry , but I am still in the dark ; if you can explain what this means :
A1 is selected to begin (but empty). Once A1 has text, row 2 will show and A2 must be selected. A2 is currently empty. Once A2 has text, row 3 will show and A3 must be selected.
Do the cells get text populated in them while the code is running ?

If the cells are empty to start with , what is the meaning of "once A1 has text" ; either A1 has text when the code is executed , or it is blank when the code is executed ; the code has actions to be carried out for both these situations within the For ... Next loop. I am not able to understand what other situation needs to be taken care of.

Anyway , I hope someone else understands and helps you out.

Narayan
 
Back
Top