Prev: Select specific worksheets & copy - code problem
Next: Replace Worksheetfunction.ln code
From: Mike Fogleman on 11 Mar 2010 06:31
This should do it:

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

Mike F
"Aussie Bob C" <AussieBobC(a)discussions.microsoft.com> wrote in message
news:D1B81B79-6A4E-45B6-8F0C-38E584549725(a)microsoft.com...
> 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: Aussie Bob C on 15 Mar 2010 03:40
Hi OssieMac

Thanks for the code with the If/Else/Endif safety added.
Just what I needed.

--
Thank you

Aussie Bob C
Little cost to carry knowledge with you.
Win XP P3 Office 2007 on Mini Mac using VMware.


"OssieMac" wrote:

> 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.
First  |  Prev  | 
Pages: 1 2
Prev: Select specific worksheets & copy - code problem
Next: Replace Worksheetfunction.ln code