visible cell to delete

vmjan02

Well-known Member
Joined
Aug 15, 2012
Messages
1,047
Office Version
  1. 365
  2. 2021
  3. 2019
This code does all perfect only to delete the visible cells it give me an error, may help

VBA Code:
Sub CopyPartOfFilteredRange()
    Dim src As Worksheet
    Dim tgt As Worksheet
    Dim filterRange As Range
    Dim copyRange As Range
    Dim lastRow As Long

    Set src = ThisWorkbook.Sheets("DB - RawData")
    Set tgt = ThisWorkbook.Sheets("Sheet12")
    
    tgt.Cells.Clear

    ' turn off any autofilters that are already set
    src.AutoFilterMode = False

    ' find the last row with data in column A
    lastRow = src.Range("A" & src.Rows.Count).End(xlUp).Row

    ' the range that we are auto-filtering (all columns)
    Set filterRange = src.Range("A7:DD7" & lastRow)

    ' the range we want to copy (only columns we want to copy)
    ' in this case we are copying country from column A
    ' we set the range to start in row 2 to prevent copying the header
    Set copyRange = src.Range("A7:DI" & lastRow)

    ' filter range based on column B
    filterRange.AutoFilter field:=106, Criteria1:="<>"
    filterRange.AutoFilter field:=107, Criteria1:="<>"
    filterRange.AutoFilter field:=108, Criteria1:="<>"
        
    ' copy the visible cells to our target range
    ' note that you can easily find the last populated row on this sheet
    ' if you don't want to over-write your previous results
    copyRange.SpecialCells(xlCellTypeVisible).Copy tgt.Range("A7")
    
    
   src.Select
   filterRange.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete  ' This part is not working
End Sub
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
You don't actually need the specialcells as the copy will only copy the visible cells if autofiltering
 
Upvote 0
You don't actually need the specialcells as the copy will only copy the visible cells if autofiltering
first it will copy the special cells to other sheet and then will delete from the pervious sheet (DB-sheet) in this case.
 
Upvote 0
As I stated you don't need SpecialCells(xlCellTypeVisible) when autofiltering

This line
VBA Code:
Set filterRange = src.Range("A7:DD7" & lastRow)
should be
VBA Code:
Set filterRange = src.Range("A7:DD" & lastRow)
Although I doubt that is causing your issue.

What does the error message say?
 
Upvote 0
As I stated you don't need SpecialCells(xlCellTypeVisible) when autofiltering

This line
VBA Code:
Set filterRange = src.Range("A7:DD7" & lastRow)
should be
VBA Code:
Set filterRange = src.Range("A7:DD" & lastRow)
Although I doubt that is causing your issue.

What does the error message say?
thanks mark, i missed the range part. now its fixed. thanks once again.
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top