Referencing a dynamic column

  • Thread starter Thread starter ChrisBat
  • Start date Start date
C

ChrisBat

How do I reference a column that changes number of rows day to day?
I am running a macro that is filtering on a number of criteria, and I
want the macro to select the first value, replace that value with a new
value, and replace all the other values returned by the filter (the VBA
equivalent of Filter, Copy, SHIFT-CTRL-Down arrow key and Paste).
I am sure that it is something fairly simple, but I've been trying for
four or five hours (doing other stuff at the same time), and I'm
stumped. I've tried using Relative Reference in Macro Recorder, but
for some reason, it does not seem to want to cooperate with me.
Any suggestions would be greatly appreciated.
Thank you for your time,
Chris
 
Chris,

I'm not sure what you're asking. If you want to determine if a particular
cell in a column has been hidden by the autofilter, examine the hidden
property of its entire row:

For Each thing In Range("MyRange")
If thing.EntireRow.Hidden = False Then thing.value= ...

If you want to know how far down the the values go, use

Range("B2").End(xlDown)

This returns the last cell with something in it.
 
I apologise - I've got a couple of big reports due today and was not
really paying attention to my post.
I download a report once a week, and have to filter on certain criteria
in two different columns. The number of tickets in each criteria
changes based on our active workload, and depending on the criteria, I
have to classify them as A, B, or C. When I filter, up to this point I
have been typing in "A" in the first cell of the returned data, and
then copying it all the way down the column by SHIFT-CTRL and down
arrow, going back to my filter, selecting the second criteria, typing
"B", and on. What I'm hoping is that there is a way to write a macro
to do this for me, considering that the volume of tickets can range
from 1 to 10,000 on any given day for any given criteria.
 
Hi Chris,

Something like this maybe. Seems I got this from Wilson.

Sub TheRange()
Dim lRow As Long
Dim dRange As Range
lRow = Cells(Rows.Count, "A").End(xlUp).Row
Set dRange = Range("A1:A" & lRow)
'dRange.Select
End Sub

HTH
Regards,
Howard
 
Chris,

Here's a way to do it manually. After applying your autofilters, select to
the bottom of your column (select the topmost visible cell, then use
Ctrl-Down -- I think there has to be something in the cells for this to
work). Now type your A, and press Ctrl-Enter.
 
Back
Top