Conditionally dete the row datas

[shinchan]

vba code will be placed in a seperate file
If cells in column B are positive then delete all the data including column B in that row except column A
save and close the file

 

[Marc L]

• Use a helper column testing with a formula if B >= 0
• Sort the entire range on that column so all the TRUE (rows to delete) are grouped at end
• Clear the TRUE rows and the helper column …

 

[shinchan]

Sir i have to do this process more than 1000 times Sir
That's y i need vba to do the same

 

[vletm]

shinchan
Do You know which one ...?
Conditionally dete the row datas? ( = Your thread ... means that there could be LESS rows )
or
clear data from B-column if value >= 0? ( = Your text in #1 reply ... means that there will be same number rows )

 

[Marc L]

As per forum rules, shinchan start to edit the thread title as "dete" means nothing …​

​

As it no needs 1 000 times to treat a workbook but just one !​

​

 

[shinchan]

i am unable to edit plz help sir

 

[vletm]

shinchan
Do You know - what should there be written?

 

[Marc L]

i am unable to edit plz help sir

Just open eyes and look at the bottom of your initial post … ​

​

 

[shinchan]

Plz edit i am unable to find edit option Sir
Sorry for the same

 

[Marc L]

I wanna see a screen capture of your initial post and post #2 from your computer …​

​

 

[Marc L]

As post #2 is an easy way found by children which is the faster way for big / huge data​

- as clearing at once is far faster than deleting row by row within any loop -​

activating the Macro Recorder and reproducing the same manually will give your code base !​

If an optimization is needed, post this generated code here …​

​

 

[Leonardo1234]

Sir just see my condition and give me the code of only that much and i will write the rest code open till save i just need the condition in a vba that part only

 

[Marc L]

Wow ! Two logins for the same price ! LoL ! ​

​

Show me your code attempt at least …​

​

 

[Leonardo1234]

Sub Mysub
 
   dim wbk1 as workbook
   dim wsh1 as worksheet
 
   Application.ScreenUpdating = False
 
   Set wbk1 = Workbooks.Open(ThisWorkbook.Path & "\sample1.xlsx")
   Set wsh1 = wbk1.Worksheets(1)


 
   Application.DisplayAlerts = False
   wbk1.Close SaveChanges:=True
   wbk2.Close SaveChanges:=True
   Application.DisplayAlerts = True
 
   Application.ScreenUpdating = True
 
end sub

Plz see

 

[Marc L]

Again a bad copy / paste with no logic inside relative to the need !​

As it's very not a good idea to declare object variables to use them only once and to forget to release them before the end.​

​

Another easy way like any beginner can use : filter the column on positive then clear the columns …​

​

So according to your initial 'explanation' instead of using the post #2 best logic or the easy filter way​

both faster for big data as avoiding a loop, a very beginner starter demonstration :​

Sub DemoPL0()
                     Dim Rw As Range
    With ActiveWorkbook
        If .Name = "PL.xlsx" Then
            With .ActiveSheet.[A1].CurrentRegion.Rows
                For Each Rw In .Item("2:" & .Count)
                      If Rw.Cells(2).Value2 >= 0 Then Rw.Offset(, 1).Clear
                Next
            End With
               .Close True
        End If
    End With
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[Leonardo1234]

Thnx Marc L Sir for giving ur precious time and Great Support to this post
Have a Great Day

 

[Marc L]

I'll tell the young summer job who wrote the original code, he will be so pleased​

until I'll show him why it's the worst way with big data !​

​

 

[Leonardo1234]

Sir i am using High end Pc with 16gb ram
I think that whatever help everyone is providing i am happy with it they are giving their precious time and help for my problems its a big thing for me
i cant say that this is good or bad or plz make better code for me its a help good or bad everything is good

 

[Marc L]

First, see post #10 …​

​