Variable Range

  • Thread starter Thread starter Martin
  • Start date Start date
M

Martin

On my worksheet, I have values in columns A-H. The following code has worked
up to now, but I have just realised that if values in any other column
exceed Column A, they are likely to be deleted (which I don't want).

Set DelRangea = [a1].End(xlDown).Offset(1, 0)
Set DelRangeb = DelRangea.End(xlDown).Offset(-1, 0)

Range(DelRangea, DelRangeb).EntireRow.Delete

In a situation where I have, say, values in cells A1:A8; B1:B8; C1:C8;
D1:D14; E1:E14; F1:F14; G1:G12; H1:H12, can DelRangea be extended so as to
include Columns B to H so that the rows to be deleted are just the blank
rows, leaving one blank (i.e., Row 15 for above example) as a divider?

Thank you for any help
Martin
 
The state of the worksheet before you execute the code is not
unambiguously defined. So, it is hard to offer any specific advice.
Consider using ActiveSheet.UsedRange or [a1].CurrentRegion. If neither
meets your requirements, loop through the various columns and keep
track of the largest row number encountered.

Also the code you shared doesn't delete all rows except the first empty
row. It deletes all rows except 65536. If the intent is to delete all
unused rows, it cannot be done. An XL worksheet will always have 65536
rows (and 256 columns).

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Tushar,

Firstly, my apologies for not replying sooner, I've only just seen your
message on Google as I was searching again for the same subject. It doesn't
appear on my newsreader even now.

I appreciate your comment on the ambiguity in this instance. A more accurate
description of the code would probably be:

***********************************
[a1:a60].EntireRow.Insert 'move yesterday's results down

<Code to import various results, organised into 8 columns>

Set DelRangea = [a1].End(xlDown).Offset(1, 0)
Set DelRangeb = DelRangea.End(xlDown).Offset(-1, 0)

Range(DelRangea, DelRangeb).EntireRow.Delete
**********************************

The overall region to be considered would therefore consist of (maximum)
[a1:h60], but is more likely to consist of the first fifteen or so rows with
data and the remainder being blank. These blank rows are the ones I wish to
delete.

Following your comment and further Google searches, I'll now start looking
at CurrentRegion, CountA, Intersect and Union. If those don't suit, then
looping through the various columns is hardly a big job is it?

Thanks again

Martin

From: Tushar Mehta ([email protected])
Subject: Re: Variable Range

View this article only
Newsgroups: microsoft.public.excel.programming
Date: 2003-10-12 09:11:03 PST

The state of the worksheet before you execute the code is not
unambiguously defined. So, it is hard to offer any specific advice.
Consider using ActiveSheet.UsedRange or [a1].CurrentRegion. If neither
meets your requirements, loop through the various columns and keep
track of the largest row number encountered.

Also the code you shared doesn't delete all rows except the first empty
row. It deletes all rows except 65536. If the intent is to delete all
unused rows, it cannot be done. An XL worksheet will always have 65536
rows (and 256 columns).

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

On my worksheet, I have values in columns A-H. The following code has
Click to expand...
worked
 
It's good you found the reply through Google. Most people would have
started new discussions on the subject.

As long as there is at least one empty row between the new data and the
old data, [a1].currentregion should give you an object that you can
manipulate further. Something like

Sub testIt()
Dim CurrRegion As Range
Range("1:60").EntireRow.Insert

'add new data
Range("a1").Value = "new 1"

Set CurrRegion = Range("a1").CurrentRegion
With CurrRegion
Range(.Rows.Count + 1 & ":" & 59).EntireRow.Delete
End With
End Sub

Note that the code above has not safety check on overwriting or
deleting existing data.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

Tushar,

Firstly, my apologies for not replying sooner, I've only just seen your
message on Google as I was searching again for the same subject. It doesn't
appear on my newsreader even now.

I appreciate your comment on the ambiguity in this instance. A more accurate
description of the code would probably be:

***********************************
[a1:a60].EntireRow.Insert 'move yesterday's results down

<Code to import various results, organised into 8 columns>

Set DelRangea = [a1].End(xlDown).Offset(1, 0)
Set DelRangeb = DelRangea.End(xlDown).Offset(-1, 0)

Range(DelRangea, DelRangeb).EntireRow.Delete
**********************************

The overall region to be considered would therefore consist of (maximum)
[a1:h60], but is more likely to consist of the first fifteen or so rows with
data and the remainder being blank. These blank rows are the ones I wish to
delete.

Following your comment and further Google searches, I'll now start looking
at CurrentRegion, CountA, Intersect and Union. If those don't suit, then
looping through the various columns is hardly a big job is it?

Thanks again

Martin

From: Tushar Mehta ([email protected])
Subject: Re: Variable Range

View this article only
Newsgroups: microsoft.public.excel.programming
Date: 2003-10-12 09:11:03 PST

The state of the worksheet before you execute the code is not
unambiguously defined. So, it is hard to offer any specific advice.
Consider using ActiveSheet.UsedRange or [a1].CurrentRegion. If neither
meets your requirements, loop through the various columns and keep
track of the largest row number encountered.

Also the code you shared doesn't delete all rows except the first empty
row. It deletes all rows except 65536. If the intent is to delete all
unused rows, it cannot be done. An XL worksheet will always have 65536
rows (and 256 columns).

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

On my worksheet, I have values in columns A-H. The following code has
Click to expand...
worked
Click to expand...
 
Back
Top