VBA macro

[Ishaqur]

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

 

[Ishaqur]

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

 

[jolivanes]

https://docs.microsoft.com/en-us/office/vba/api/excel.range.removeduplicates

 

[Ishaqur]

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

 

[Ishaqur]

Countif Manuel formula it’s work fine with filter I remove all record it’s shows more than 1

Pl see advise with VBA

 

[LMacD]

wrong thread - sorry

 

[jolivanes]

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.

 

[jolivanes]

In the previous post, change this

.Offset(, -2).SpecialCells(12).ClearContents

to this

.Offset(1, -2).SpecialCells(12).ClearContents

 

[Ishaqur]

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

 

[jolivanes]

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

 

[Ishaqur]

Is there any way I can upload excel file