need code help or correction

VBABEGINER

Well-known Member
Joined
Jun 15, 2011
Messages
1,232
Can any one pls help here..
Filter Col AB as a blank values. Select first row after header and give cell name as "False". Then copy this "False" value for rest of the filtered rows.

VBA Code:
LastRow = Range("A2", Range("A" & Rows.Count).End(xlUp)).Count

Range("AB1").Select
ActiveSheet.Range("$A$1:$AM$" & LastRow).AutoFilter Field:=28, Criteria1:="="

Dim r As Long
r = Range("A2:A" & Rows.Count).SpecialCells(xlVisible)(1).Row

Range("AB" & r).Select
ActiveCell.FormulaR1C1 = "FALSE"

'To get last row number after filter the COl AB
Dim FinalRowFiltered As Long, dataRange As Range
Set dataRange = Range("$AB$2:$AB$" & LastRow)
With dataRange.SpecialCells(xlCellTypeVisible)
    FinalRowFiltered = .Areas(.Areas.Count).Row + .Areas(.Areas.Count).Rows.Count
    'MsgBox FinalRowFiltered
End With

Range("AB" & r).Select
Selection.Copy
'instead of below line..
Range("AB3:AB" & FinalRowFiltered).Select
'I need in this way
Range("AB"&r+1 ":" "AB" & FinalRowFiltered).Select

Selection.SpecialCells(xlCellTypeVisible).Select
ActiveSheet.Paste
Application.CutCopyMode = False

Range("AB1").Select
ActiveSheet.Range("$A$1:$AM$" & LastRow).AutoFilter Field:=28, Criteria1:="False"
 
Combine the Copy line with the Paste line and put this:-
VBA Code:
    .Range(.Range("AO2"), .Range("AO2").End(xlToRight)).Copy Destination:=.Range("AO3:AO" & LastRow + 3)
 
Upvote 0

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Combine the Copy line with the Paste line and put this:-
VBA Code:
    .Range(.Range("AO2"), .Range("AO2").End(xlToRight)).Copy Destination:=.Range("AO3:AO" & LastRow + 3)
Thank You Alex.. for copy and paste in a single line, this is the Best code line.. Thank You so much for understanding query well and giving us solution in order to complete my work.. 🙇‍♂️
 
Upvote 0
Alex, how do copy-paste work if we've copy in one with-end with section and pasting with another with-end with section..?
VBA Code:
Dim lr As Long, lc As Long
        lr = .Cells(.Rows.Count, "A").End(xlUp).Row
        lc = .Cells(1, .Columns.Count).End(xlToLeft).Column
        
        .Range(.Cells(1, "A"), .Cells(lr, lc)).Copy
    
    End With
        
        
    Set WB_PP = Workbooks.Open(Filename:="D:\...\PP.xlsx")
    Set WS_PP = ActiveSheet
    
    With WS_PP
''        Dim end_row As Long
''        end_row = .Cells(.Rows.Count, "A").End(xlUp).Row
        LastRow = .Range("A" & .Rows.Count).End(xlUp).Row

        .Range("A" & LastRow + 1).Paste 'here I'm getting error..
        Application.CutCopyMode = False
 
Upvote 0
Alex, how do copy-paste work if we've copy in one with-end with section and pasting with another with-end with section..?
VBA Code:
Dim lr As Long, lc As Long
        lr = .Cells(.Rows.Count, "A").End(xlUp).Row
        lc = .Cells(1, .Columns.Count).End(xlToLeft).Column
       
        .Range(.Cells(1, "A"), .Cells(lr, lc)).Copy
   
    End With
       
       
    Set WB_PP = Workbooks.Open(Filename:="D:\...\PP.xlsx")
    Set WS_PP = ActiveSheet
   
    With WS_PP
''        Dim end_row As Long
''        end_row = .Cells(.Rows.Count, "A").End(xlUp).Row
        LastRow = .Range("A" & .Rows.Count).End(xlUp).Row

        .Range("A" & LastRow + 1).Paste 'here I'm getting error..
        Application.CutCopyMode = False
just adding next code line after cutcopy mode (last line)
.Rows("LastRow+1:LastRow+2").Delete Shift:=xlUp ... This also not working

any best solution for this please..
 
Upvote 0
If WS_PP is different to the sheet that you are copying from then you need to use PasteSpecial rather than Paste.

For the other one try (untested)

VBA Code:
.Rows(""" & Lastrow+1 & ":" & LastRow+2 & """).Delete Shift:=xlUp
 
Upvote 0
You might also find that opening the workbook is knocking you out of copy mode
 
Upvote 0
If WS_PP is different to the sheet that you are copying from then you need to use PasteSpecial rather than Paste.

For the other one try (untested)

VBA Code:
.Rows(""" & Lastrow+1 & ":" & LastRow+2 & """).Delete Shift:=xlUp
Hi Mark,
Thank You for your quick reply and help. I used PasteSpecial. For the untested line, it is giving me compile error, expected: list separator or )

Mark, I'm testing it and copy mode is available, its not de-highlight..
 
Upvote 0
Just to clarify where we are at & make an adjustment to Mark's last post.

1) Replace
VBA Code:
 .Range("A" & LastRow + 1).Paste 'here I'm getting error..
With this:
VBA Code:
.Range("A" & LastRow + 1).PasteSpecial

2) Replace
VBA Code:
.Rows("LastRow+1:LastRow+2").Delete Shift:=xlUp ... This also not working
With this:
VBA Code:
.Rows(LastRow + 1 & ":" & LastRow + 2).Delete Shift:=xlUp
 
Upvote 0
Solution
Just to clarify where we are at & make an adjustment to Mark's last post.

1) Replace
VBA Code:
 .Range("A" & LastRow + 1).Paste 'here I'm getting error..
With this:
VBA Code:
.Range("A" & LastRow + 1).PasteSpecial

2) Replace
VBA Code:
.Rows("LastRow+1:LastRow+2").Delete Shift:=xlUp ... This also not working
With this:
VBA Code:
.Rows(LastRow + 1 & ":" & LastRow + 2).Delete Shift:=xlUp
Yes, correctly worked.. I really appreciate Alex for your every dedicated reply and help. Thanks Mark again for your kind help..
 
Upvote 0
Alex / Mark,

With WS.PP, I'm trying to find if any duplicate values are there in Col A or not.. I got one code from our forum and tried to edit but I'm getting error on
VBA Code:
rn = .Range("A" & .Rows.Count).End(xlUp).Row
wherein, entire code is,
Code:
Set WB_PP = Workbooks.Open(Filename:="D:\...\PP.xlsx")
Set WS_PP = ActiveSheet
    
With WS_PP
        LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        .Range("A" & LastRow + 2).PasteSpecial
        Application.CutCopyMode = False

        .Rows(LastRow + 1 & ":" & LastRow + 2).Delete Shift:=xlUp
        LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        .Range("A" & LastRow).Select
        
        If .AutoFilterMode Then .AutoFilterMode = False
        Dim rn As Range
        rn = .Range("A" & .Rows.Count).End(xlUp).Row 'error getting here - run time error 91 - object variable or with block variable not set
        rn.FormatConditions.AddUniqueValues
        rn.FormatConditions(rn.FormatConditions.Count).SetFirstPriority
        rn.FormatConditions(1).DupeUnique = xlDuplicate
        rn.FormatConditions(1).Interior.Color = 255
        rn.FormatConditions(1).StopIfTrue = False
        WS_PP.Range("A1:G1").AutoFilter Field:=7, Criteria1:=RGB(255, 0, 0), Operator:=xlFilterCellColor
        Sheets.Add(, Sheets(Sheets.Count)).Name = "Duplicate"
        WS_PP.AutoFilter.Range.EntireRow.Copy Range("A1")
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

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