Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable Range
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable Range
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 In article , says... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Variable Range | Excel Discussion (Misc queries) | |||
variable range | New Users to Excel | |||
Variable range in VB | Excel Discussion (Misc queries) | |||
variable range | Excel Programming | |||
Problem trying to us a range variable as an array variable | Excel Programming |