Prev: Select specific worksheets & copy - code problem
Next: Replace Worksheetfunction.ln code
From: Aussie Bob C on 11 Mar 2010 05:56
From Leith Ross 2/7/2006

Dim EndCell As Range
Set EndCell = ActiveSheet.Cells(Rows.Count, "A").End(xlUp)
ActiveSheet.Range(ActiveCell, EndCell).ClearContents

If I change the A to an N it selects a range from ActiveCell down to last
entry in Column N.
My ActiveCell is in Column A but Column N may not have data down to the
last cell as in Column A

How do I select a range of cells from an ActiveCell in column A across to
Column N and down to last data cell in column A

--
Thank you

Aussie Bob C
Little cost to carry knowledge with you.
Win XP P3 Office 2007 on Mini Mac using VMware.
From: Mike H on 11 Mar 2010 06:09
Hi,

Try this with the usual caveat that it is very unlikely you need to select
the range to do what you want. The last line could simply be

MyRangeA.ClearContents


Dim MyRangeA As Range
Dim LastRowA As Long
LastRowA = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set MyRangeA = Range("A" & ActiveCell.Row & ":N" & LastRowA)
MyRangeA.Select
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Aussie Bob C" wrote:

> From Leith Ross 2/7/2006
>
> Dim EndCell As Range
> Set EndCell = ActiveSheet.Cells(Rows.Count, "A").End(xlUp)
> ActiveSheet.Range(ActiveCell, EndCell).ClearContents
>
> If I change the A to an N it selects a range from ActiveCell down to last
> entry in Column N.
> My ActiveCell is in Column A but Column N may not have data down to the
> last cell as in Column A
>
> How do I select a range of cells from an ActiveCell in column A across to
> Column N and down to last data cell in column A
>
> --
> Thank you
>
> Aussie Bob C
> Little cost to carry knowledge with you.
> Win XP P3 Office 2007 on Mini Mac using VMware.
From: joel on 11 Mar 2010 06:08

Dim EndCell As Range
Dim FirstRow as Integer
Dim LastRow as Integer
FirstRow = ActiveSheet.ActiveCell.Row
LastRow ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

Set DataRange = ActiveSheet.Range("N" & FirstRow & ":N" & LastRow)
DataRange.ClearContents


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=186589

[url="http://www.thecodecage.com/forumz/"]Excel Live Chat[/url]

From: OssieMac on 11 Mar 2010 06:16
Hello Aussie Bob,

Whether you use the If/Else/EndIf is up to you but I should think that you
would not want code selecting the range if you have not previously selected a
cell in the correct column.

Having said that, normally with code it is not necessary to actually select
ranges but not knowing what you are doing with the code it is hard to advise
the best way for your particular case.

Sub SelectSpecific()

Dim lastRow As Long

With ActiveSheet
If ActiveCell.Column = Columns("A").Column Then
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range(ActiveCell, .Cells(lastRow, "N")).Select
Else
MsgBox "Activecell not in column A"
End If
End With

End Sub


--
Regards,

OssieMac


"Aussie Bob C" wrote:

> From Leith Ross 2/7/2006
>
> Dim EndCell As Range
> Set EndCell = ActiveSheet.Cells(Rows.Count, "A").End(xlUp)
> ActiveSheet.Range(ActiveCell, EndCell).ClearContents
>
> If I change the A to an N it selects a range from ActiveCell down to last
> entry in Column N.
> My ActiveCell is in Column A but Column N may not have data down to the
> last cell as in Column A
>
> How do I select a range of cells from an ActiveCell in column A across to
> Column N and down to last data cell in column A
>
> --
> Thank you
>
> Aussie Bob C
> Little cost to carry knowledge with you.
> Win XP P3 Office 2007 on Mini Mac using VMware.
From: Roger Govier on 11 Mar 2010 06:25
Hi Bob

Dim lr as long, fr as long
with activesheet
fr=activecell.row
lr = cells(Rows.count("A").End(Xlup).Row
range("A" & fr & ":N" & lr).clearcontents
end with

--
Regards
Roger Govier

Aussie Bob C wrote:
> From Leith Ross 2/7/2006
>
> Dim EndCell As Range
> Set EndCell = ActiveSheet.Cells(Rows.Count, "A").End(xlUp)
> ActiveSheet.Range(ActiveCell, EndCell).ClearContents
>
> If I change the A to an N it selects a range from ActiveCell down to last
> entry in Column N.
> My ActiveCell is in Column A but Column N may not have data down to the
> last cell as in Column A
>
> How do I select a range of cells from an ActiveCell in column A across to
> Column N and down to last data cell in column A
>
 |  Next  |  Last
Pages: 1 2
Prev: Select specific worksheets & copy - code problem
Next: Replace Worksheetfunction.ln code