Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    Aug 2001
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Find next blank cell in column (2000)

    Again thanks everyone here for their invaluable help. I was wondering what the little snippet (i'm sure) of code finds the next available cell, or the last occupied row. What I want to do is, I want to apply a border to only the cells that have been occupied.

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find next blank cell in column (2000)

    Assuming that you mean that you want a border around a selected range, and not each individual cell then the following will apply a border to the current region.

    Sub Border_Current_Region()
    Selection.CurrentRegion.Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    End Sub

    Just place your cursor somewhere in the region and run the macro.

    I hope this helps.

  3. #3
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find next blank cell in column (2000)

    Kieran,

    Just a pointer here.

    Using "selection" is much less efficient than using "range". Selections physically move cursors etc in the workbook, and can be quite inefficient. You probably would not notice it in the code you've posted- but if, for instance, you were copying values from one workbook to another using selections, you would notice a huge difference.

    Dim rngData As Range
    Set rngData = Selection
    with rngData
    .Borders(xlDiagonalDown).LineStyle = xlNone
    .Borders(xlDiagonalUp).LineStyle = xlNone
    with .Borders(xlEdgeLeft)


    etc
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find next blank cell in column (2000)

    This code will find the row number of the last used row in Column A:

    <pre>Dim lLastRow
    lLastRow = Range("A65536").End(xlUp).Row
    </pre>


    This code will set the object variable to the range of the last cell in column A:

    <pre>Dim oLastRow As Range
    Set oLastRow = Range("A65536").End(xlUp)
    </pre>

    Legare Coleman

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find next blank cell in column (2000)

    Geoff,

    I knew from previous posts on this forum that ranges were normally more efficient, however I did not realise the perfomance hit that would occur using 'selection'. I will keep it in mind in future.

    Thanks for the tip.

  6. #6
    Star Lounger
    Join Date
    Aug 2001
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find next blank cell in column (2000)

    Thank you all once again for your timely help.

  7. #7
    Star Lounger
    Join Date
    Aug 2001
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find next blank cell in column (2000)

    Thanks, this helped me do a major function of my project, which is a student information form filler driven by UserForms. I'm trying to apply this End(xlUp) stuff to do a rectangular selection on only the populated area of the sheet, but I can't figure it out. What I want to do is, draw a rectangular border around the area of the sheet that has text in it (the upper left). I probably want to do a Range select starting from A0, but I can't get the value for the lower right corner (which is determined by how many cells are populated.

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Find next blank cell in column (2000)

    If the block of data you wish to border constitutes the entire contents of the sheet you could use :- <pre> Sub SetBorder1()
    ActiveSheet.UsedRange.BorderAround _
    ColorIndex:=xlAutomatic, Weight:=xlMedium
    End Sub</pre>

    or if there is other data in seperate ranges the following will place a border around the area containing cell A1 use :-<pre> Sub SetBorder2()
    Range("A1").CurrentRegion.BorderAround _
    ColorIndex:=xlAutomatic, Weight:=xlMedium
    End Sub</pre>

    xlAutomatic can be replaced by the color index value you want, and xlMedium can be replaced by xlThin, xlThick etc.

    Andrew C

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find next blank cell in column (2000)

    I don't think that the End property works on a range that contains more than one column. If UsedRange or CurrentRegion will not work for you, then I think that you would have to loop through all of the columns in the range and find the one with the largest last row number.
    Legare Coleman

  10. #10
    Star Lounger
    Join Date
    Aug 2001
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find next blank cell in column (2000)

    Good algorithm, Legare. Thanks once again!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •