dynamic cell selection

Afro_Cookie

Board Regular
Joined
Mar 17, 2020
Messages
103
Office Version
  1. 365
Platform
  1. Windows
I am using =Datedif() to determine time frame of some stock. It's currently set into a pivot table as I only need some of the data from the main workbook, which is different to the workbook I am using. Because not each row has a date reference, there are a number of them that show a #VALUE! error. I've added =IFERROR() to hide these, simply to make it look pretty.

I have a macro that hides the cells with the correct value (14), but I want to select the cells below it until the next non error cell as well. The reason I want to hide them is so that I can generate an e-mail of visible cells and send it out.

Any help would be appreciated.

VBA Code:
Sub Example()

Dim cel As Range, rng As Range
Set rng = Range("A1", Range("A500").End(xlUp))

For Each cel In rng
      If cel.Value <= 14 Then
      cel.EntireRow.Hidden = True
End If

Next cel

End Sub
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
I made this a lot more complicated that I should have.

Can someone help me write some code to find the first instance of "14" and then select all rows below that? After that I can simply hide the selection
VBA Code:
Selection.EntireRow.Hidden = True
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

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