Offset from active cell until given range

KyleJackMorrison

Board Regular
Joined
Dec 3, 2013
Messages
107
Office Version
  1. 365
  2. 2021
  3. 2019
Platform
  1. Windows
Hey,

So here is my code and i require it to offset select cells to the left until J23 and to the right until NO23.

Code:
Sub hideme()


Sheets("Settings").Visible = True
Sheets("Main").Select
For Each cell In ActiveSheet.Range("J23:NO23")
    If cell.Value = [Today()] Then
    cell.Select
    End If
Next
Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, [U]NO23[/U])).EntireColumn.Hidden = True
Range(ActiveCell.Offset(0, -1), ActiveCell.Offset(0, [U]J23[/U])).EntireColumn.Hidden = True

'do something
End Sub

In theory, if the cell X23 contained today's date. All columns to the right get hidden until column 'NO' and all columns to the left get hidden until J23.
 
Last edited:

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
How about
Code:
Sub hideme()
Dim Cell As Range

Sheets("Settings").Visible = True
Sheets("Main").Select
For Each Cell In ActiveSheet.Range("J23:NO23")
    If Cell.Value = [Today()] Then
    Select Case Cell.Column
      Case 10
         Range("K:NO").EntireColumn.Hidden = True
      Case 379
         Range("J:NN").EntireColumn.Hidden = True
      Case Else
         Range("J23", Cells(23, Cell.Column - 1)).EntireColumn.Hidden = True
         Range(Cells(23, Cell.Column + 1), Range("NO23")).EntireColumn.Hidden = True
      End Select
    End If
    Exit Sub
Next
End Sub
 
Upvote 0
As far as me testing this it didn't work. Nothing changed

Just a question, as the code works for Today(), can this then be adapted as easily as Today()+1, Today()+2.. etc?
 
Upvote 0
What do you mean "Nothing changed"?
Do you have today's date somewhere in the range J23:NO23?
 
Upvote 0
Why do you have this?
Code:
Exit Sub
That will exit the loop after the first iteration, i.e. after the value in J23 has been checked.

If it should be anywhere that line of code should be within the If..End If.
 
Upvote 0
So inputting that code and running it made no change to the sheet. No columns hidden.
And not even an activecell selected just the previous activecell prior to running the code.

So today's date is actually in AS23 formatted dd/mm/yy.
 
Upvote 0
Good spot Norie
The code should be
Code:
Sub hideme()
Dim Cell As Range

Sheets("Settings").Visible = True
Sheets("Main").Select
For Each Cell In ActiveSheet.Range("J23:NO23")
    If Cell.Value = [Today()] Then
      Select Case Cell.Column
        Case 10
           Range("K:NO").EntireColumn.Hidden = True
        Case 379
           Range("J:NN").EntireColumn.Hidden = True
        Case Else
           Range("J23", Cells(23, Cell.Column - 1)).EntireColumn.Hidden = True
           Range(Cells(23, Cell.Column + 1), Range("NO23")).EntireColumn.Hidden = True
      End Select
      Exit Sub
    End If
Next Cell
End Sub
This assumes that the date only appears once in the row.
 
Upvote 0
Awesome thank you! I can confirm that this is exactly what i needed. big up the Fluff and Norie.
 
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top