VBA macro

Ishaqur

New Member
Joined
May 23, 2019
Messages
15
I have 1700 hundred record some are debit and some are credit I need to move credit value in debit column with negative sign as value
 
Thank you for your help I need one more favour in VBA code that I have thousand of serial number in column C and I need to delete the row is serial number come more than once I use manual formula countif and delete all record which sowing 2 or 3 or 4 so on

How I can write a macro to delete the row more the serial number comes more than once
 
Upvote 0

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Thank you for reply remove-duplicate remove one and keep one but I need to delete both record if it’s comes as more than one time

For example serial no 44 comes two time I need to delete both serial no 44

But with remove duplicate it’s remove one 44 and keep other
 
Upvote 0
Countif Manuel formula it’s work fine with filter I remove all record it’s shows more than 1

Pl see advise with VBA
 
Upvote 0
Code:
Sub Maybe()
Dim lr As Long
lr = Cells(Rows.Count, 3).End(xlUp).Row
Application.ScreenUpdating = False

    With Range("C2:C" & lr).Offset(, 2)    '<----- Column E is empty and free to use
        .Formula = "=COUNTIF(R2C[-2]:R" & lr & "C[-2],RC[-2])"
        .Value = .Value
    End With
    
    With Range("E1:E" & lr)
        .AutoFilter Field:=1, Criteria1:=">1"
        .Offset(, -2).SpecialCells(12).ClearContents
    End With
    
ActiveSheet.AutoFilterMode = False
Columns(5).ClearContents
Application.ScreenUpdating = True
End Sub

It assumes that you have headers in Row 1 and that Column E is empty and free to use.
Change references if and where required.
 
Upvote 0
In the previous post, change this
Code:
.Offset(, -2).SpecialCells(12).ClearContents
to this
Code:
.Offset(1, -2).SpecialCells(12).ClearContents
 
Last edited:
Upvote 0
Thank you one more. Question I have to copy column f g h data from monitor file and want to add in main file below last entry of column f g and h

And also add serial number in e like last drool number in column e is 2014 if we copied four record from monitor file serial number should go 2018

And also want to copy c and d last data in next for record

Regards
 
Upvote 0
I am sorry but without an example with a before and after, I am totally lost about what you need.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,213
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