In the code below, the part ' Clear the contents from B6 to the last row in L…
- 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 = TrueEnd Sub
- Assistant
The issue with the code is that the
Deletemethod used in the linesourceWS.Range("B6:L" & lastCell.Row).Delete Shift:=xlUpdeletes 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 thexlShiftUpmethod of theRowsobject.Replace the following code:
sourceWS.Range("B6:L" & lastCell.Row).Delete Shift:=xlUpwith the following code:
Dim clearRange As Range Set clearRange = sourceWS.Range("B6:L" & lastRow) clearRange.ClearContents clearRange.Rows.Delete Shift:=xlShiftUpThis code will first clear the contents of the range B6 to the last row in L using the
ClearContentsmethod. Then, it will delete the rows of the range without deleting the entire range itself, using thexlShiftUpmethod of theRowsobject. This will move the cells below the cleared contents up to row 6.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.