Expanding Selection

  • Thread starter Thread starter aposatsk
  • Start date Start date
A

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.
 
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 said:
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
Click to expand...

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).
 
Find the lastrow (pick out any of those columns (AF???))

then you could use:

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