Expanding Selection

[aposatsk]

My spreadsheet is as follows:

(1)XXXXXX|XXXXXXX|XXXXXXXX|XXXXXXX|XXXXXXX|XXXXXXXXX|XXXXX|
(2)XXXXXX|XXXXXXX|1-----------|2----------|3----------|XXXXXXXXX|XXXXX|
(3)XXXXXX|XXXXXXX|345453453|32434234|32434234|XXXXXXXXX|XXXXX|
(4)XXXXXX|XXXXXXX|654645644|12314356|61626536|XXXXXXXXX|XXXXX|
(5)XXXXXX|XXXXXXX|345453453|32434234|32434234|XXXXXXXXX|XXXXX|
(6)XXXXXX|XXXXXXX|345453453|32434234|32434234|XXXXXXXXX|XXXXX|
(7)XXXXXX|XXXXXXX|XXXXXXXX|XXXXXXX|XXXXXXX|XXXXXXXXX|XXXXX|
(8)XXXXXX|XXXXXXX|XXXXXXXX|XXXXXXX|XXXXXXX|XXXXXXXXX|XXXXX|

The XXXXXXX represents text in columns that are parallel to columns 1,
2, and 3.

My macro works with only Rows 2 to 6 and columns labelled 1, 2, 3. It
does many operations with these rows. *However*, when I add new rows,
the selection from say C2:C6 no longer works, since new rows have been
added. So, the selection C1:C6 has to be expanded to additional rows,
such as C1:C10.

HOW DO I ALLOW THE EXCEL MACRO TO RECOGNIZE WHICH ROWS HAVE BEEN ADDED,
AND THEN AUTOMATICALLY EXPAND EVERY SELECTION?
In other words, my spreadsheet has changed from:

(1)XXXXXX|XXXXXXX|XXXXXXXX|XXXXXXX|XXXXXXX|XXXXXXXXX|XXXXX|
(2)XXXXXX|XXXXXXX|1---------|2--------|3--------|XXXXXXXXX|XXXXX|
(3)XXXXXX|XXXXXXX|345453453|32434234|32434234|XXXXXXXXX|XXXXX|
(4)XXXXXX|XXXXXXX|654645644|12314356|61626536|XXXXXXXXX|XXXXX|
(5)XXXXXX|XXXXXXX|345453453|32434234|32434234|XXXXXXXXX|XXXXX|
(6)XXXXXX|XXXXXXX|345453453|32434234|32434234|XXXXXXXXX|XXXXX|
(7)XXXXXX|XXXXXXX|XXXXXXXX|XXXXXXX|XXXXXXX|XXXXXXXXX|XXXXX|
(8)XXXXXX|XXXXXXX|XXXXXXXX|XXXXXXX|XXXXXXX|XXXXXXXXX|XXXXX|

TO
(1)XXXXXX|XXXXXXX|XXXXXXXX|XXXXXXX|XXXXXXX|XXXXXXXXX|XXXXX|
(2)XXXXXX|XXXXXXX|1---------|2--------|3--------|XXXXXXXXX|XXXXX|
(3)XXXXXX|XXXXXXX|345453453|32434234|32434234|XXXXXXXXX|XXXXX|
(4)XXXXXX|XXXXXXX|654645644|12314356|61626536|XXXXXXXXX|XXXXX|
(5)XXXXXX|XXXXXXX|345453453|32434234|32434234|XXXXXXXXX|XXXXX|
(6)XXXXXX|XXXXXXX|345453453|32434234|32434234|XXXXXXXXX|XXXXX|
(7)XXXXXX|XXXXXXX|345476553|32486734|32434234|XXXXXXXXX|XXXXX|
(8)XXXXXX|XXXXXXX|345453453|32111234|32434234|XXXXXXXXX|XXXXX|
(9)XXXXXX|XXXXXXX|XXXXXXXX|XXXXXXX|XXXXXXX|XXXXXXXXX|XXXXX|
(10)XXXXX|XXXXXXX|XXXXXXXX|XXXXXXX|XXXXXXX|XXXXXXXXX|XXXXX|

And I need the macro to work with the newly added rows.

 

[Dave Peterson]

It really depends on what your macro does and how it does it.

If you can loop through the rows, maybe you could use column A to find the last
row.

Dim FirstRow as long
dim LastRow as long
dim iRow as long

with worksheets("Sheet1")
firstrow = 2
lastrow = .cells(.rows.count,"A").end(xlup).row
for irow = firstrow to lastrow
'do stuff
next irow

'or even work from the bottom up
for irow = lastrow to firstrow step -1
'do stuff
next irow
end with

 

[aposatsk]

It really depends on what your macro does and how it does it.

If you can loop through the rows, maybe you could use column A to find
the last
row.

Dim FirstRow as long
dim LastRow as long
dim iRow as long

with worksheets("Sheet1")
firstrow = 2
lastrow = .cells(.rows.count,"A").end(xlup).row
for irow = firstrow to lastrow
'do stuff
next irow

'or even work from the bottom up
for irow = lastrow to firstrow step -1
'do stuff
next irow
end with

Dave Peterson

My spreadsheet looks something like this:
(1)---------(2)----------(3)---------(4)
Date1------Date2------Date3------Date4
Date2------Date3------Date4------Date5
XXXX------XXXXX------XXXX------XXXX
XXXX------XXXXX------XXXX------XXXX
XXXX------XXXXX------XXXX------XXXX
XXXX------XXXXX------XXXX------XXXX

Now the macro takes column 1 and deletes it. Then it copies Column 2,
3, 4 and places it in place of column 1. Basically, you have a deletion
of Column 1 and then a shift of the leftover one column to the left.
Column 4 is then empty, and the dates are generated.

The macro itself is:

Code:
--------------------
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 15/05/2006 by Alex_Posatskiy
'

'
Range("X9:AD174").Select
Range("X9:AF174").Select
Range("AF9").Activate
Selection.Copy
Range("W9").Select
ActiveSheet.Paste
Range("AF9:AF174").Select
Selection.ClearContents
Range("W4").Select
With Worksheets("Asphalt").Range("w7")
.Value = .Value + 7
End With
End Sub



--------------------


The "174" is the last row of my chart. However, the macro obviously
does not work if i have a 175th row (which is what I am trying to make
possible).

 

[Dave Peterson]

Find the lastrow (pick out any of those columns (AF???))

then you could use:

Range("AF9:AF" & lastrow).Select