Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0613· openaiHugging Face Suzhen/CodeChat

In the code below, the part ' Clear the contents from B6 to the last row in L…

  1. User

    In the code below, the part ' Clear the contents from B6 to the last row in L sourceWS.Range("B6:L" & lastRow).ClearContents does exactly what I want it to do, But the part of the code after, 'Dim lastCell As Range Set lastCell = sourceWS.Range("L" & Rows.count).End(xlUp) If lastCell.Row > 6 Then sourceWS.Range("B6:L" & lastCell.Row).Delete Shift:=xlUp End If' Is deleting all the data below row 6.

    What I want the code to do is as follows. Only Delete the cells of the cleared contents, and move the cells that were below the cleared contents up to row 6

    Public Sub ServiceProvidersHistory()

    Application.EnableEvents = False
    
    Dim sourceWB As Workbook
    Dim sourceWS As Worksheet
    Dim historyWB As Workbook
    Dim historyWS As Worksheet
    Dim lastRow As Long
    Dim pasteRow As Long
    
    ' Set source workbook and worksheet
    Set sourceWB = ActiveWorkbook
    Set sourceWS = sourceWB.ActiveSheet
    
    ' Find the first empty cell in column I
    Dim firstEmptyCell As Range
    Set firstEmptyCell = sourceWS.Range("I:I").Find("", LookIn:=xlValues, LookAt:=xlWhole)
    
    ' Check if an empty cell is found
    If Not firstEmptyCell Is Nothing Then
        ' Calculate the last row to copy based on the first empty cell in column I
        lastRow = firstEmptyCell.Row - 1
    End If
    
    ' Copy the range from B6 to the last row in L
    sourceWS.Range("B6:L" & lastRow).Copy
    
    ' Open the history workbook
    Set historyWB = Workbooks.Open("G:\Shared drives\Swan School Site Premises\PREMISES MANAGEMENT\SERVICE PROVIDERS\Service Providers History.xlsx")
    Application.Wait (Now + TimeValue("0:00:15"))
    ' Select the sheet with the same name as the active sheet in the source workbook
    On Error Resume Next
    Set historyWS = historyWB.Sheets(sourceWS.Name)
    On Error GoTo 0
    
    ' If the sheet doesn't exist, create a new sheet with the same name
    If historyWS Is Nothing Then
        sourceWS.Copy After:=historyWB.Sheets(historyWB.Sheets.count)
        Set historyWS = historyWB.ActiveSheet
        historyWS.Name = sourceWS.Name
    End If
    
    ' Activate the history workbook, select the history sheet
    historyWB.Activate
    historyWB.Sheets(sourceWS.Name).Select
    
    ' Find the next empty row in the history sheet
    pasteRow = historyWS.Cells(historyWS.Rows.count, "B").End(xlUp).Row + 1
    
    ' Paste the values from the clipboard into the history sheet
    historyWS.Range("B" & pasteRow).PasteSpecial Paste:=xlPasteValues
    
    ' Save and close the history workbook
    historyWB.Save
    historyWB.Close
    
    ' Clear the contents from B6 to the last row in L
    sourceWS.Range("B6:L" & lastRow).ClearContents
    

    ' Delete the cells from B6 to the last row in L and move the cells up to row 6 Dim lastCell As Range Set lastCell = sourceWS.Range("L" & Rows.count).End(xlUp) If lastCell.Row > 6 Then sourceWS.Range("B6:L" & lastCell.Row).Delete Shift:=xlUp End If

    ' Insert today's date in C5 to I5
    Dim currentDate As Date
    currentDate = Date
    sourceWS.Range("C5:I5").Value = currentDate
    
    ' Display a message box
    MsgBox "Completed tasks have been backed up to History"
    
    ' Enable all events
    Application.EnableEvents = True
    

    End Sub

  2. Assistant

    The issue with the code is that the Delete method used in the line sourceWS.Range("B6:L" & lastCell.Row).Delete Shift:=xlUp deletes the entire range, including the cleared cells. To achieve the desired result of only deleting the cleared cells and moving the cells below up to row 6, you can use the xlShiftUp method of the Rows object.

    Replace the following code:

    sourceWS.Range("B6:L" & lastCell.Row).Delete Shift:=xlUp
    

    with the following code:

    Dim clearRange As Range
    Set clearRange = sourceWS.Range("B6:L" & lastRow)
    clearRange.ClearContents
    clearRange.Rows.Delete Shift:=xlShiftUp
    

    This code will first clear the contents of the range B6 to the last row in L using the ClearContents method. Then, it will delete the rows of the range without deleting the entire range itself, using the xlShiftUp method of the Rows object. This will move the cells below the cleared contents up to row 6.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.