Hindsight is 20/20. Or you live and you learn.

Good luck.

davegb wrote:

It has occurred to me that this might have been easier had I made 3
sheets for each category (Of course, I didn't know when I started it
would work out this way. After I gave my end-users the original data,
they asked for the back-up records that determined the first round
data.) But it would make sense to create a sheet for user data, a
datasheet for the records, and a macro sheet which would hold the
information that the macros use to create the "Records" sheet based on
the selection in the user sheet. After I get this next round of data
out to them, I'll have some time, I hope, and restructure the whole
thing to make it easier to manage.

Dave Peterson wrote:
Maybe it's better to work down and to the right:

Option Explicit
Sub test()
Dim LastRow As Long
Dim LastCol As Long
Dim wksht As Worksheet

Set wksht = ActiveSheet

With wksht
LastRow = .Range("a1").End(xlDown).Row
LastCol = .Range("a1").End(xlToRight).Column

.Range("a1", .Cells(LastRow, LastCol)).Name = "filterrange"
End With

End Sub


If this didn't work, can you share the address of the table? And
what's
adjacent to it that should be avoided?

For what it's worth, I've always tried to keep my tables on separate
worksheets. If I mix tables (or even other stuff) on that same
worksheet, it
becomes just a little more difficult to delete or insert rows or
columns.





davegb wrote:

Thanks, Dave!
The macro runs now, but is selecting data outside the table (other
info
located further right in the spreadsheet. Is there a way to only
select
the data in the table?

Dave Peterson wrote:
Oopsie. I left out a closing paren:

This:
.range("a1",.cells(lastrow,lastcol).name = "filterrange"
should be:
.range("a1",.cells(lastrow,lastcol)).name = "filterrange"

Sorry.

davegb wrote:

Thanks, Dave!
I've changed the order of the steps so it goes across the top
first,
which, as you said, has no blanks because of the column
headers.
And
I've made sure that the right column has no blanks. Should take
care of
that problem.
I've discovered in discussion with the person feeding me the
data
from
SPSS that this latest data, and some of the future data, comes
from
an
entirely different database than what she had given before, and
has
blanks and some serious formatting problems when downloaded
into
Excel.
Blank cells, blank cells that aren't really blank, leading and
trailing
spaces, etc. Fortunately, I've gleaned some good programs here
and
elsewhere to help with most of this.

As for your code, it won't run. First try, I got a compile
error on
filterrange. I dimmed it as a range, and now I get a run error
on
the
line:
.range("a1",.cells(lastrow,las*tcol).name = "filterrange"

I tried it with and without the quotes. I also turned it
around,
because it seemed to me that filterrange should be defined as
the
range
from a1 to lastrow, lastcol, not the other way around. Is there
a
time
when you define variables backwards, like here?
I also tried changing it to xltoRight, then xlDown, because it
seemed
to me that from A1 I want to go to the right, then down to
define
the
filterrange, not up and left. Is it just me, or does it seem
like
everything in this code is backwards? What am I missing?
Any other ideas?

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson