used range causing error

[JKK22]

Hello
Is there a better way to accomplish the below , rather than using used range?
Also, is there a better way to clear the contents of cells D:I all in one, rather than listing each out?

Purpose of Macro:
Step 1. INPUT Tab: Remove all data in Columns D:I , if the text in column D = “PMFA” . Keep all others. ( this clears out the current day data and prepares the sheet for tomorrow)
Step 2. INPUT tab: Re-sort the data in columns D: I, sorting by Column D, Largest -smallest. ( this should put the non-PMFA data on the top)
Step 3. Go to “PMFA” tab within the same workbook and copy values from only the filled/used in data from columns Q:V . ( there could be data from cells Q2: V500, but we only want the cells that have data, for example in Q2: Q30)
Step 4: Go back to the Input tab in this same workbook, and append the data as values into columns D:I , on the next available row in column D.

Note: columns A-C on the Input tab have formulas used for another purpose, so they can’t be ignored.
Note: this code works, but sometimes we have problems using used range ( cells look blank, but the macro thinks they are used)

________________________________________________________________________________________

Sub INPUT_AddPMFA()

Dim g As Long

For g = 2 To ActiveSheet.UsedRange.Rows.Count

If Cells(g, "D").Value = "PMFA" Then

Cells(g, "D").ClearContents

Cells(g, "E").ClearContents

Cells(g, "F").ClearContents

Cells(g, "G").ClearContents

Cells(g, "H").ClearContents

Cells(g, "I").ClearContents

End If

Next

ThisWorkbook.Activate

Sheets("PMFA").Select

Range("Q2:V500").Select

Selection.SpecialCells(xlCellTypeVisible).Select

Application.CutCopyMode = True

Selection.Copy

Sheets("Input").Range("D" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

Application.CutCopyMode = False

Range("D1").Select



End Sub

 

[aaewalsh]

Yes, there are better ways to accomplish the tasks in the macro.

For step 1, instead of using the used range and checking each cell in columns D:I, you can use the AutoFilter method to filter out the rows that contain "PMFA" in column D, and then use the Delete method to delete those rows. This way, you don't have to clear the contents of each cell individually.

For step 2, instead of using a loop to sort the data, you can use the Sort method to sort the data in columns D:I by column D in descending order.

For step 3, instead of copying the entire range Q2:V500 and then selecting the visible cells, you can use the SpecialCells method to select only the used cells in the range, and then copy those cells.

For step 4, instead of pasting the data at the next available row in column D, you can use the ListObject method to create a table and then append the data to that table.

Here's an example of what the modified macro could look like:

Sub INPUT_AddPMFA()

Dim inputSheet As Worksheet
Dim pmfaSheet As Worksheet

Set inputSheet = ThisWorkbook.Sheets("Input")
Set pmfaSheet = ThisWorkbook.Sheets("PMFA")

With inputSheet
    .Range("A1:I" & .Cells(.Rows.Count, "A").End(xlUp).Row).AutoFilter Field:=4, Criteria1:="PMFA"
    .Range("A2:I" & .Cells(.Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Delete
    .Range("A1:I" & .Cells(.Rows.Count, "A").End(xlUp).Row).AutoFilter
    .Range("A1:I1").Sort Key1:=.Range("D1"), Order1:=xlDescending
End With

With pmfaSheet
    .Range("Q2:V" & .Cells(.Rows.Count, "Q").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy
    inputSheet.Range("D" & inputSheet.Cells(inputSheet.Rows.Count, "D").End(xlUp).Row + 1).PasteSpecial xlPasteValues
End With

Application.CutCopyMode = False

End Sub

This is a better alternative, it reduces the number of iterations, and it is more efficient.

 

[JKK22]

Hello and thank you for your reply. This does work, however, we don't want to delete the entire row, because there is other data in other columns that we don't want deleted.
Also, the formulas in columns A:C will eventually get used up unless we copy them down to row 100,000 or something. We really just want the data in D:I to be cleared out.
Do you have any other suggestions?

 

[aaewalsh]

Try this:

Sub INPUT_AddPMFA()

Dim inputSheet As Worksheet Dim pmfaSheet As Worksheet

' Declare and set variables for the worksheets "Input" and "PMFA" Set inputSheet = ThisWorkbook.Sheets("Input") Set pmfaSheet = ThisWorkbook.Sheets("PMFA")

With inputSheet 'Filter the data in the "Input" worksheet by the value "PMFA" in column D .Range("A1:I" & .Cells(.Rows.Count, "A").End(xlUp).Row).AutoFilter Field:=4, Criteria1:="PMFA"

'Clear the contents of the cells in columns D:I but keep the data in the other columns intact
.Range("D2:I" & .Cells(.Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeVisible).ClearContents

'Remove the filter
.Range("A1:I" & .Cells(.Rows.Count, "A").End(xlUp).Row).AutoFilter

'Sort the data in the "Input" worksheet by the value in column D in descending order
.Range("A1:I1").Sort Key1:=.Range("D1"), Order1:=xlDescending

'Copy the formulas in columns A:C down to the last row of the sheet
.Range("A1:C" & .Cells(.Rows.Count, "A").End(xlUp).Row).FillDown

End With

'Copy the visible data in the "PMFA" worksheet's columns Q to V With pmfaSheet .Range("Q2:V" & .Cells(.Rows.Count, "Q").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy End With

'Paste the data in the "Input" worksheet's column D after the last row with data. inputSheet.Range("D" & inputSheet.Cells(inputSheet.Rows.Count, "D").End(xlUp).Row + 1).PasteSpecial xlPasteValues

'Reset the clipboard Application.CutCopyMode = False

End Sub