Search code examples
excelexcel-2007vba

Modify the pasting of data (constraining the range)


I am using the below code:

Dim LastRow As Integer, i As Integer, erow As Integer

    LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To LastRow
        If Cells(i, 2) = "1" Then

            ' As opposed to selecting the cells, this will copy them directly
            Range(Cells(i, 1), Cells(i, 26)).Copy

            ' As opposed to "Activating" the workbook, and selecting the sheet, this will paste the cells directly
            With Workbooks("Swivel - Master - January 2016.xlsm").Sheets("Swivel")
                erow = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
                .Cells(erow, 1).PasteSpecial xlPasteAll
            End With
            Application.CutCopyMode = False
        End If
    Next I

This works as it is suppose to, but I need to constrain the range that is pastes to. When this code is run, it copies the range of A2:Z2 (sample range for this question, it actually copies more rows than this), but it pastes to cells beyond column Z. I am most concerned with column AD as there is code to change the text of that row green when there is a value inserted into that column. After the copy/paste code is run, the row changes to green text, even though there is nothing in AD. Here is the code that changes the text to green in the rows (this code is in the Sheet1 object of the workbook).

Private Sub Worksheet_Change(ByVal Target As Range)
'
Dim r As Range
Set r = Target.EntireRow

    If Target.row = 1 Then Exit Sub ' Don't change header color

    If r.Cells(1, "AD").Value <> "" Then
        r.Font.Color = RGB(0, 176, 80)
    Else
        r.Font.ColorIndex = 1
    End If
End Sub

Now, in column AD of a row that we complete the work on, we insert a date and time in this format: 1/4/2016 13:20. Can I change this line:

If r.Cells(1, "AD").Value <> "" Then

to check for the format rather than the value?

I am still learning VBA, but know that I have a lot to learn. Any assistance is appreciated.

EDIT: The anomaly does not occur until after this code is run (which is located in the target workbook "Swivel"):

Sub Remove_Duplicates()
'
Application.ScreenUpdating = False

    ActiveSheet.Range("$A$1:$Z$2000").RemoveDuplicates Columns:=Array(10, 11, 12, 13, 14, 15, 16), Header:=xlYes
    ActiveWindow.SmallScroll Down:=6

Range("C" & Rows.Count).End(xlUp).Offset(1).Select

Application.ScreenUpdating = True

End Sub

This changes the text to the green color, but there is nothing in column AD of that row to trigger the change.


Solution

  • Instead of trying to resolve this in the Worksheet_Change sub, I modified the Remove_Duplicates sub to this:

    Sub Remove_Duplicates()
    '
    Application.ScreenUpdating = False
    
    Dim usedrng As Range
    
        ActiveSheet.Range("$A$1:$Z$2000").RemoveDuplicates Columns:=Array(5, 10, 11, 12, 13, 14, 15, 16), Header:=xlYes
    
        For Each usedrng In ActiveSheet.UsedRange
            If usedrng.Value = "" Then
                usedrng.ClearContents
            End If
        Next
    
    Range("C" & Rows.Count).End(xlUp).Offset(1).Select
    
    Application.ScreenUpdating = True
    
    End Sub
    

    This has removed the fake empty values that I was contending with effectively. The WorkSheet_Change sub now works as it is written in the initial question above and the worksheet behaves in the manner it should when new rows of data are added, which is that the text should remain black until a date/time entry is made in column AD for that row.

    Thanks to all those who offered their assistance. I hope this answer helps others as well.