Data Validation
 

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.

Data Validation
 

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.

Data Validation
 

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

Data Validation
 

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