Results 1 to 7 of 7

Offset only visible cells

This is a discussion on Offset only visible cells within the Excel Questions forums, part of the Question Forums category; Hi everyone, I have this code below and was hoping to get help modifying it to offset only only visible ...

  1. #1
    Board Regular
    Join Date
    Jun 2010
    Location
    Seattle Wa.
    Posts
    600

    Default Offset only visible cells

    Hi everyone, I have this code below and was hoping to get help modifying it to offset only only visible cells.

    Code:
    If ActiveSheet.Range("X34").Value > 0 Then
    HrBdg = ActiveSheet.Range("X34").Value
    For i = 1 To HrBdg
    Range("X33").End(xlUp).Offset(1).Value = "1"
    Next i
    End If
    Thanks in advance for any help.

    sd

  2. #2
    Board Regular
    Join Date
    Mar 2012
    Location
    United States
    Posts
    337

    Default Re: Offset only visible cells

    Quote Originally Posted by sdoppke View Post
    Hi everyone, I have this code below and was hoping to get help modifying it to offset only only visible cells.

    Code:
    If ActiveSheet.Range("X34").Value > 0 Then
    HrBdg = ActiveSheet.Range("X34").Value
    For i = 1 To HrBdg
    Range("X33").End(xlUp).Offset(1).Value = "1"
    Next i
    End If
    Thanks in advance for any help.

    sd
    This seems like a painful solutions, but there is a hidden property you could check, documented here. To do this you'd have to check and if both the row and column of that cell are visible then run your desired code. You might want to wait and see if anyone else has a better idea, but I figured I'd post this in case you didn't get any other responses. Good luck!
    ~Mathchick

  3. #3
    Board Regular
    Join Date
    Aug 2009
    Location
    Southern Finland
    Posts
    1,231

    Default Re: Offset only visible cells

    This might not be the best possible way to do it but it seems to work:
    Code:
    Dim c As Range
    Dim Rng As Range
    Dim HrBdg As Long
    
    If ActiveSheet.Range("X34").Value > 0 Then
        
        HrBdg = ActiveSheet.Range("X34").Value
        
        Set c = Range("X33").End(xlUp)
        Set Rng = Range(c, Range("X33")).SpecialCells(xlCellTypeVisible)
            
        If Rng.Cells.Count > HrBdg Then
            
            For i = 1 To HrBdg
    Back:
                Set c = c.Offset(1)
                
                    If Not Intersect(c, Rng) Is Nothing Then
                        c.Value = "1"
                    Else
                        GoTo Back
                    End If
            Next i
        Else
            'What if there's not enough visible cells?
        End If
    End If

  4. #4
    Board Regular
    Join Date
    Jun 2010
    Location
    Seattle Wa.
    Posts
    600

    Default Re: Offset only visible cells

    I came up with this idea, but it seems to think rows are hiddend when they are not?

    Code:
    If ActiveSheet.Range("X34").Value > 0 Then
    Hrs = ActiveSheet.Range("X34").Value
    For i = 1 To Hrs
    Range("X33").End(xlUp).Offset(1).Select
    Do Until ActiveCell.EntireRow.Hidden = False
    Range("X33").End(xlUp).Offset(1).Value = "1"
    Loop
    Next i
    End If
    sd

  5. #5
    Board Regular
    Join Date
    Jun 2010
    Location
    Seattle Wa.
    Posts
    600

    Default Re: Offset only visible cells

    Quote Originally Posted by Misca View Post
    This might not be the best possible way to do it but it seems to work:
    Code:
    Dim c As Range
    Dim Rng As Range
    Dim HrBdg As Long
     
    If ActiveSheet.Range("X34").Value > 0 Then
     
        HrBdg = ActiveSheet.Range("X34").Value
     
        Set c = Range("X33").End(xlUp)
        Set Rng = Range(c, Range("X33")).SpecialCells(xlCellTypeVisible)
     
        If Rng.Cells.Count > HrBdg Then
     
            For i = 1 To HrBdg
    Back:
                Set c = c.Offset(1)
     
                    If Not Intersect(c, Rng) Is Nothing Then
                        c.Value = "1"
                    Else
                        GoTo Back
                    End If
            Next i
        Else
            'What if there's not enough visible cells?
        End If
    End If
    This worked good. I appreciate the help. Im going to go out on a limb and ask if you can help me take it one step further...

    Would you know howmost efficiently, run the same code over and over till it completes a range. to clarify, instead of starting with column x. I would start form column D (run this scrip and then do the same in every other column, until I hit column BR.

    Well thanks for hangin in there and for any help or direction.

    sd

  6. #6
    Board Regular
    Join Date
    Aug 2009
    Location
    Southern Finland
    Posts
    1,231

    Default Re: Offset only visible cells

    Try:
    Code:
    Sub EveryOtherColumn()
    
    Dim j As Integer
    Dim c As Range
    Dim Rng As Range
    Dim HrBdg As Long
    
    For j = 4 To 70 Step 2
        If Cells(34, j).Value > 0 Then
        
        HrBdg = Cells(34, j).Value
     
        Set c = Cells(33, j).End(xlUp)
        Set Rng = Range(c, Cells(33, j)).SpecialCells(xlCellTypeVisible)
     
           If Rng.Cells.Count > HrBdg Then
        
               For i = 1 To HrBdg
    Back:
                   Set c = c.Offset(1)
        
                       If Not Intersect(c, Rng) Is Nothing Then
                           c.Value = "1"
                       Else
                           GoTo Back
                       End If
               Next i
           Else
               'What if there's not enough visible cells?
           End If
        End If
    Next j
    
    End Sub

  7. #7
    Board Regular
    Join Date
    Jun 2010
    Location
    Seattle Wa.
    Posts
    600

    Default Re: Offset only visible cells

    Quote Originally Posted by Misca View Post
    Try:
    Code:
    Sub EveryOtherColumn()
     
    Dim j As Integer
    Dim c As Range
    Dim Rng As Range
    Dim HrBdg As Long
     
    For j = 4 To 70 Step 2
        If Cells(34, j).Value > 0 Then
     
        HrBdg = Cells(34, j).Value
     
        Set c = Cells(33, j).End(xlUp)
        Set Rng = Range(c, Cells(33, j)).SpecialCells(xlCellTypeVisible)
     
           If Rng.Cells.Count > HrBdg Then
     
               For i = 1 To HrBdg
    Back:
                   Set c = c.Offset(1)
     
                       If Not Intersect(c, Rng) Is Nothing Then
                           c.Value = "1"
                       Else
                           GoTo Back
                       End If
               Next i
           Else
               'What if there's not enough visible cells?
           End If
        End If
    Next j
     
    End Sub

    Again, worked perfect, thank you so much.

    sd

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com