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
Bookmarks