Re: Get next empty Column?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: James (j_at_j.net)
Date: 09/17/04 

Date: Fri, 17 Sep 2004 18:53:57 -0400

Thanks ALOT!! That did the trick!!

"Norman Jones" <normanjones@whereforartthou.com> wrote in message
news:uvBz2NQnEHA.3396@tk2msftngp13.phx.gbl...
> Hi James,
>
> I omitted to qualify the code to refer to Sheet2:
>
> Dim Rng As Range
> Const rw As Long = 1 '<<====== CHANGE
>
> With Sheets("Sheet2") '<<====== CHANGE
> If IsEmpty(.Cells(rw, 1)) Then
> Set Rng = .Cells(rw, 1)
> Else
> Set Rng = .Cells(rw, Columns.Count). _
> End(xlToLeft)(1, 2)
> End If
> End With
> MsgBox Rng.Address
>
>
> ---
> Regards,
> Norman
>
>
>
> "Norman Jones" <normanjones@whereforartthou.com> wrote in message
> news:OLu56AQnEHA.4004@TK2MSFTNGP10.phx.gbl...
> > Hi James,
> >
> > To set a variable Rng to the first empty cell in row 1 on sheet2, try:
> >
> > Dim Rng As Range
> > Const rw As Long = 1 '<<====== CHANGE
> > If IsEmpty(Cells(rw, 1)) Then
> > Set Rng = Cells(rw, 1)
> > Else
> > Set Rng = Cells(rw, Columns.Count). _
> > End(xlToLeft)(1, 2)
> > End If
> > MsgBox Rng.Address
> >
> >
> > Change the rw value to reflect the row that you use to test for an empty
> > column.
> >
> >
> > ---
> > Regards,
> > Norman
> >
> >
> >
> > "James" <j@j.net> wrote in message
> > news:eXZA1sPnEHA.3968@TK2MSFTNGP11.phx.gbl...
> >>I want the button to copy from one sheet and each time I'd like it to
> >>place
> >> the numbers in the next available column
> >>
> >> Do I need to loop and check for emptiness?
> >>
> >> Thanks
> >>
> >>
> >
> >
>
>

Relevant Pages

  • Re: Place rectangle and define its height?
    ... Dim rng As Range ... MsgBox rng.Height ... kind regards ...
    (microsoft.public.excel.programming)
  • Re: Increasing a range in a loop
    ... > Sub Tester11() ... > Dim rng As Range, ... > MsgBox rng1.Address ...
    (microsoft.public.excel.programming)
  • Re: Finding a cell
    ... > Dim rng as Range ... > msgbox rng.Address ... > Tom Ogilvy ...
    (microsoft.public.excel.programming)
  • Re: Count rows On error MsgBox
    ... Dim rng as Range ... msgbox "Bad data, data should fill A1:N10" ... > The mention of blank cells was to indicate the rows of> data in column A contained blank cells, not I wanted to> know if blank cells where there. ... > Regards Bob C.> ...
    (microsoft.public.excel.programming)
  • Re: Newsgroup Reader Advice
    ... Regards, James ... Gary Keramidas wrote: ... server: ...
    (microsoft.public.excel.programming)