Is is possible to programicably set a range
dependant on the values in certain fields -
then delete that range ?

Thanks - Kirk

Short answer: Yes.

I think we'd need more details to give more help. You can build up a string
to hold the start/end addresses of the range, or even rows/columns if you're
looking to delete in that fashion, then Set a range variable to that address.
Quick, short example:
If X=1 Then
anyAddress = "A1:"
End If
if Y=9 then
anyAddress = anyAddress & "Z44"
End If
Set rngVariable = Range(anyAddress).
.... then go on to clear or delete the range. Obviously your logic for
setting up the anyAddress variable will be more complex than I've shown above.

"kirkm" wrote:

Is is possible to programicably set a range
dependant on the values in certain fields -
then delete that range ?

Thanks - Kirk

On Sat, 28 Apr 2007 23:38:02 -0700, JLatham <HelpFrom @
Jlathamsite.com.(removethis) wrote:

To be honest I'm not sure what you mean.
You're seting a range from A1 to Z44 depending on X and Y ?

I'm also not sure if my definition of 'range' is correct. Ignoring the
delete part of it, is a range consecutive? Rows 1 to 100? Or could
it contain only rows 10, 15, 30 etc. ?

Then if that is so, can it delete rows 10, 15, 30 ... ?

Thanks - Kirk

Short answer: Yes.

I think we'd need more details to give more help. You can build up a string
to hold the start/end addresses of the range, or even rows/columns if you're
looking to delete in that fashion, then Set a range variable to that address.
Quick, short example:
If X=1 Then
anyAddress = "A1:"
End If
if Y=9 then
anyAddress = anyAddress & "Z44"
End If
Set rngVariable = Range(anyAddress).
... then go on to clear or delete the range. Obviously your logic for
setting up the anyAddress variable will be more complex than I've shown above.

"kirkm" wrote:

Is is possible to programicably set a range
dependant on the values in certain fields -
then delete that range ?

Thanks - Kirk

kirkm: You can create a union of the ranges you want to delete. Then do the
delete later. some example on this site where people do this is to remove
blank lines. they use a flter to do the union, then they delete.

"kirkm" wrote:

On Sat, 28 Apr 2007 23:38:02 -0700, JLatham <HelpFrom @
Jlathamsite.com.(removethis) wrote:

To be honest I'm not sure what you mean.
You're seting a range from A1 to Z44 depending on X and Y ?

I'm also not sure if my definition of 'range' is correct. Ignoring the
delete part of it, is a range consecutive? Rows 1 to 100? Or could
it contain only rows 10, 15, 30 etc. ?

Then if that is so, can it delete rows 10, 15, 30 ... ?

Thanks - Kirk

Short answer: Yes.

I think we'd need more details to give more help. You can build up a string
to hold the start/end addresses of the range, or even rows/columns if you're
looking to delete in that fashion, then Set a range variable to that address.
Quick, short example:
If X=1 Then
anyAddress = "A1:"
End If
if Y=9 then
anyAddress = anyAddress & "Z44"
End If
Set rngVariable = Range(anyAddress).
... then go on to clear or delete the range. Obviously your logic for
setting up the anyAddress variable will be more complex than I've shown above.

"kirkm" wrote:

Is is possible to programicably set a range
dependant on the values in certain fields -
then delete that range ?

Thanks - Kirk

Sorry for the confusion, the X and Y examples were simply meant to show that
you could programatically evaluate values and make decisions towards setting
up the range to be deleted on those evaluations.

In fact, you could be evaluating the contents of cells on a sheet directly
or any of a number of ways. No a range does not have to be consecutive. A
range can refer to "geographically separated" cells, rows or columns. It can
refer to a single cell or all the cells on a sheet.

Again, if we had a description of what it is you are trying to do, we could
be more specific in supplying assistance or a solution.

In the meantime, here's some code to demonstrate one possible way to deal
with deleting rows based on the contents of a single cell in them. To see it
in action, cut and paste this code into a standard VB code module in a
workbook. Then set up a worksheet with numbers in column A for as far down
as you care to put them. With the sheet with the numbers on it selected, run
the macro. Any rows with a value that is evenly divisible by 5 (as 5, 10,
35, 105) in the cell in column A will be deleted.

If you're a little unfamiliar with VB coding, any line ending with a " _"
tells the VB engine that the instruction continues on the next line. We use
it here because the editor here tends to break up long code lines in
unpredictable ways - ways that break the code if you cut and paste it when
that happens.

Sub DeleteSomeRows()
'this will examine the values
'in column A on a sheet and
'delete entire rows when
'a value in column A of that
'row is evenly divisible by 5
'
Dim rOffset As Long
Dim rngLastUsedCellInColA As Range
Dim rngTopOfColumn As Range

'do the in-memory equivalent of selecting the
'cell just below the last used cell in column A
'could be any column you want
Set rngLastUsedCellInColA = _
Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

'position ourselves at row 1 in the test column
Set rngTopOfColumn = Range("A1")

'work down the sheet until we get just beyond
'the end of information in the test column
Do Until rngTopOfColumn.Offset(rOffset, 0).Row _
rngLastUsedCellInColA.Row
If _
rngTopOfColumn.Offset(rOffset, 0).Value Mod 5 = 0 _
Then
rngTopOfColumn.Offset(rOffset, 0).EntireRow.Delete
End If
rOffset = rOffset + 1
Loop
End Sub



"kirkm" wrote:

On Sat, 28 Apr 2007 23:38:02 -0700, JLatham <HelpFrom @
Jlathamsite.com.(removethis) wrote:

To be honest I'm not sure what you mean.
You're seting a range from A1 to Z44 depending on X and Y ?

I'm also not sure if my definition of 'range' is correct. Ignoring the
delete part of it, is a range consecutive? Rows 1 to 100? Or could
it contain only rows 10, 15, 30 etc. ?

Then if that is so, can it delete rows 10, 15, 30 ... ?

Thanks - Kirk

Short answer: Yes.

I think we'd need more details to give more help. You can build up a string
to hold the start/end addresses of the range, or even rows/columns if you're
looking to delete in that fashion, then Set a range variable to that address.
Quick, short example:
If X=1 Then
anyAddress = "A1:"
End If
if Y=9 then
anyAddress = anyAddress & "Z44"
End If
Set rngVariable = Range(anyAddress).
... then go on to clear or delete the range. Obviously your logic for
setting up the anyAddress variable will be more complex than I've shown above.

"kirkm" wrote:

Is is possible to programicably set a range
dependant on the values in certain fields -
then delete that range ?

Thanks - Kirk