Selecting/Copying Variable Areas
 

Hi

I have a template which uses formulas to drag data from an input sheet
through to a worksheet which manipulates the data. The number of rows of data
on the input sheet is variable. I cannot use activerange because I do not
want to select cells containing formulas which have not resulted in data
being pulled through. What I need is a macro which checks the rows in column
A to see how many have text data pulled through and ignors the rest where
there is a formula but which has not dragged data from the input sheet and
then to select these rows.

This selection would start at A4 (or the user could select the start cell
manually). The area would be limited by the last column which contains text
in any row (and again not formulas).

Thanks for any assistance.

Selecting/Copying Variable Areas
 

Dim lastrow as Long, realLastRow as Long
lastrow = cells(rows.count,1).End(xlup)
for i = lastrow to 4 step -1
if len(trim(cells(i,1).text)) 0 then
reallastrow = i
exit for
end if
Next
Range("A4:A" & reallastrow).Select

would be a start.

--
Regards,
Tom Ogilvy


"Constantly Amazed" wrote:

Hi

I have a template which uses formulas to drag data from an input sheet
through to a worksheet which manipulates the data. The number of rows of data
on the input sheet is variable. I cannot use activerange because I do not
want to select cells containing formulas which have not resulted in data
being pulled through. What I need is a macro which checks the rows in column
A to see how many have text data pulled through and ignors the rest where
there is a formula but which has not dragged data from the input sheet and
then to select these rows.

This selection would start at A4 (or the user could select the start cell
manually). The area would be limited by the last column which contains text
in any row (and again not formulas).

Thanks for any assistance.

Selecting/Copying Variable Areas
 

Hi Tom

Thanks for the input. However, when I try and run this code I get a run
time error '13' Type mismatch. The debug then highlights the line: lastrow =
cells(rows.count,1).End(xlup)

Can you help?

G

"Tom Ogilvy" wrote:

Dim lastrow as Long, realLastRow as Long
lastrow = cells(rows.count,1).End(xlup)
for i = lastrow to 4 step -1
if len(trim(cells(i,1).text)) 0 then
reallastrow = i
exit for
end if
Next
Range("A4:A" & reallastrow).Select

would be a start.

--
Regards,
Tom Ogilvy


"Constantly Amazed" wrote:

Hi

I have a template which uses formulas to drag data from an input sheet
through to a worksheet which manipulates the data. The number of rows of data
on the input sheet is variable. I cannot use activerange because I do not
want to select cells containing formulas which have not resulted in data
being pulled through. What I need is a macro which checks the rows in column
A to see how many have text data pulled through and ignors the rest where
there is a formula but which has not dragged data from the input sheet and
then to select these rows.

This selection would start at A4 (or the user could select the start cell
manually). The area would be limited by the last column which contains text
in any row (and again not formulas).

Thanks for any assistance.

Selecting/Copying Variable Areas
 

Dim lastrow as Long, realLastRow as Long
lastrow = cells(rows.count,1).End(xlup).row '<==
for i = lastrow to 4 step -1
if len(trim(cells(i,1).text)) 0 then
reallastrow = i
exit for
end if
Next
Range("A4:A" & reallastrow).Select

--
Regards,
Tom Ogilvy


"Constantly Amazed" wrote:

Hi Tom

Thanks for the input. However, when I try and run this code I get a run
time error '13' Type mismatch. The debug then highlights the line: lastrow =
cells(rows.count,1).End(xlup)

Can you help?

G

"Tom Ogilvy" wrote:

Dim lastrow as Long, realLastRow as Long
lastrow = cells(rows.count,1).End(xlup)
for i = lastrow to 4 step -1
if len(trim(cells(i,1).text)) 0 then
reallastrow = i
exit for
end if
Next
Range("A4:A" & reallastrow).Select

would be a start.

--
Regards,
Tom Ogilvy


"Constantly Amazed" wrote:

Hi

I have a template which uses formulas to drag data from an input sheet
through to a worksheet which manipulates the data. The number of rows of data
on the input sheet is variable. I cannot use activerange because I do not
want to select cells containing formulas which have not resulted in data
being pulled through. What I need is a macro which checks the rows in column
A to see how many have text data pulled through and ignors the rest where
there is a formula but which has not dragged data from the input sheet and
then to select these rows.

This selection would start at A4 (or the user could select the start cell
manually). The area would be limited by the last column which contains text
in any row (and again not formulas).

Thanks for any assistance.