Results 1 to 10 of 10

Find last used cell in specific range (current row) not working with xlToRight Parameter

Threaded View

mullock Find last used cell in... 04-10-2013, 03:22 PM
Oppressed1 Re: Find last used cell in... 04-10-2013, 04:03 PM
AlphaFrog Re: Find last used cell in... 04-10-2013, 04:04 PM
Alf Re: Find last used cell in... 04-10-2013, 04:08 PM
protonLeah Re: Find last used cell in... 04-10-2013, 04:40 PM
mullock Re: Find last used cell in... 04-10-2013, 05:31 PM
mullock Re: Find last used cell in... 04-12-2013, 02:49 PM
protonLeah Re: Find last used cell in... 04-10-2013, 07:58 PM
mullock Re: Find last used cell in... 04-10-2013, 09:01 PM
protonLeah Re: Find last used cell in... 04-11-2013, 09:03 PM
  1. #1
    Registered User
    Join Date
    02-12-2013
    Location
    O'Fallon, MO
    MS-Off Ver
    Excel 2010
    Posts
    8

    Find last used cell in specific range (current row) not working with xlToRight Parameter

    Ok guys, this is making me crazy (because I know it is a simple thing)

    I need to define a specific range (Current row columns 2 thru 9) with which to use My variables Party & Party Range below. The problem is in the xlToRight parameter works fine unless there is a blank cell in it then it stops. I will have lots of blank cells (the purpose is to backfill for generating some XML)Book1.xlsm

    When I use xlToLeft or xlUp I only get the first cell in the range not the last one (reading left to right --> closest to Col 9 is what I need)


    Sub OrgTree_BackFill()
       
       Dim Party As String
       Dim r As Long
       Dim c As Long
       Dim Data As String
       Dim rng As Range
       Dim ws As Worksheet
       Dim lRow As Long
       Dim PartyRng As String
       Application.ScreenUpdating = False
        
        Set ws = Worksheets("4-Org Tree")
          With ws
            If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
                lRow = .Cells.Find(What:="*", _
                              After:=.Range("A1"), _
                              Lookat:=xlPart, _
                              LookIn:=xlValues, _
                              SearchOrder:=xlByRows, _
                              SearchDirection:=xlPrevious, _
                              MatchCase:=False).Row
            Else
                lRow = 3
            End If
           End With
       For r = 3 To lRow
         For c = 2 To 9
            Cells(r, c).Activate
            
            Set rng = Range(Cells(r, 2), Cells(r, 9))
            Party = rng.End(xlToRight).Value
            PartyRng = rng.End(xlToRight).Address
                   
            ActiveCell.EntireColumn.HorizontalAlignment = xlLeft
         If Party = "" Then
            ActiveCell.EntireRow.Delete
           Else
            End If
         If ActiveCell.Address = PartyRng Then GoTo LastLine
         If c = 2 And ActiveCell.Value <> "" And ActiveCell.Value <> ActiveCell.Offset(-1, 0).Value Then GoTo LastLine
         If c < 4 And ActiveCell.Value = "" And ActiveCell.Offset(-1, 0).Value <> "" Then
            ActiveCell.Value = ActiveCell.Offset(-1, 0).Value
              Else
            End If
         If c > 3 And ActiveCell.Value = "" And ActiveCell.Offset(-1, 0).Value <> "" Then
            ActiveCell.Value = ActiveCell.Offset(-1, 0).Value
                 Else
            End If
         Cells(r, 1) = "Y"
            Next c
    LastLine:
        Next r
        Call FormulasFix
        Cells(3, 3).Formula = "=IF('2-Site Survey Admin'!C6<>"""",'2-Site Survey Admin'!C6,"""")"
        Application.ScreenUpdating = True
        Cells(4, 3).Activate
    End Sub
    Last edited by mullock; 04-12-2013 at 02:50 PM. Reason: Solved

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1