Is it possible for data validation to overwrite an existing sheet? I'm using
a macro to extract rows to a second sheet, it has space for 12 possible rows.
If the extraction is less than 12 rows, say only 8, the balance of the rows
would be already populated. Not from the last use, but from a reset button
that populates the "copy to" field with 9's. This data is then moved to a
third sheet that uses 9's instead of blank cells. Currently I'm receiving an
error when I try to overwrite the populated cells.

I know it sounds crazy, but thats the way it was set-up?

Thanks in advance.

That should be Advanced Filters, not Data Validation.

"M.A.Tyler" wrote:

Is it possible for data validation to overwrite an existing sheet? I'm using
a macro to extract rows to a second sheet, it has space for 12 possible rows.
If the extraction is less than 12 rows, say only 8, the balance of the rows
would be already populated. Not from the last use, but from a reset button
that populates the "copy to" field with 9's. This data is then moved to a
third sheet that uses 9's instead of blank cells. Currently I'm receiving an
error when I try to overwrite the populated cells.

I know it sounds crazy, but thats the way it was set-up?

Thanks in advance.

Maybe you could just populate the empty cells with 9's after you do your
advanced filter.

dim myRng as range
with worksheets("Sheet2")
set myrng = nothing
on error resume next
set myrng = .range("a1:A12").cells.specialcells(xlcelltypeblan ks)
on error goto 0
if myrng is nothing then
'no empty cells
else
myrng.value = 9
end if
end with



M.A.Tyler wrote:

Is it possible for data validation to overwrite an existing sheet? I'm using
a macro to extract rows to a second sheet, it has space for 12 possible rows.
If the extraction is less than 12 rows, say only 8, the balance of the rows
would be already populated. Not from the last use, but from a reset button
that populates the "copy to" field with 9's. This data is then moved to a
third sheet that uses 9's instead of blank cells. Currently I'm receiving an
error when I try to overwrite the populated cells.

I know it sounds crazy, but thats the way it was set-up?

Thanks in advance.

--

Dave Peterson

Thanks Dave! Worked great!

"Dave Peterson" wrote:

Maybe you could just populate the empty cells with 9's after you do your
advanced filter.

dim myRng as range
with worksheets("Sheet2")
set myrng = nothing
on error resume next
set myrng = .range("a1:A12").cells.specialcells(xlcelltypeblan ks)
on error goto 0
if myrng is nothing then
'no empty cells
else
myrng.value = 9
end if
end with



M.A.Tyler wrote:

Is it possible for data validation to overwrite an existing sheet? I'm using
a macro to extract rows to a second sheet, it has space for 12 possible rows.
If the extraction is less than 12 rows, say only 8, the balance of the rows
would be already populated. Not from the last use, but from a reset button
that populates the "copy to" field with 9's. This data is then moved to a
third sheet that uses 9's instead of blank cells. Currently I'm receiving an
error when I try to overwrite the populated cells.

I know it sounds crazy, but thats the way it was set-up?

Thanks in advance.

--

Dave Peterson