Macro needed to remove rows based on predefined condition

[kuldeepjainesl]

Hi Experts,


I have a sheet named "Output" with 1000 rows and I want a macro which can delete rows if cell is found blank in a specified coloum or a specified text and paste these deleted row to different sheet named "Invalid data".


Regards,

Kuldeep

 

[SirJB7]

Hi, kuldeepjainesl!


Tried to perform searches within this site before posting, because maybe your question had been answered yet?


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords "delete rows" and press Search button. You'd retrieve many links from this website, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.


Regards!

 

[kuldeepjainesl]

Hello SirJB7,


Thanks for the eye opener post... i realize that i must have posted with the details while starting this post. i am sorry for my slip-up.


After your kind suggestion i even searched at chandoo.org. but everyone seems to just asking to delete the blank row but i need to cut these rows from a certain sheet "Output" and to be pasted on another certain sheet "Invalid data". Also this macro need to able to first clear all the content from sheet (on which data to be pasted.)so that in case new data has few line then old data should create a problem.


Hope you can understand....as i am Electronics graduate and do not know the ABC of VB. I think that any excel formula will not be able to delete rows hence i asked your support for a macro directly.


Previously before posting it , I searched it on Google and reach to a code mentioned below: (and many more)


-------------------------------------------------------------------

DeleteRowOnCell


The macro DeleteBlankRows will delete a row if the entire row is blank. This macro will delete the entire row if a the cell in the specified column is blank. Only this column is checked. Other columns are ignored.


Public Sub DeleteRowOnCell()


On Error Resume Next

Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

ActiveSheet.UsedRange


End Sub


To use this macro, select a columnar range of cells, and then run the macro. If the cell in that column is blank, the entire row will be deleted. To process the entire column, click the column header to select the entire column.

--------------------------------------------------------------------------

This macro need user to select the coloum to search which is not possible in my case as this sheet will be hidden to user.


Regards,

Kuldeep

 

[SirJB7]

Hi, kuldeepjainesl!


Give a look at this file:

https://dl.dropbox.com/u/60558749/Macro%20needed%20to%20remove%20rows%20based%20on%20predefined%20condition%20%28for%20kuldeepjainesl%20at%20chandoo.org%29.xslm.xlsm


Then run macro MoveRowsBetweenSheets. Parameters for column and test value are in third sheet.


Regards!

 

[kuldeepjainesl]

Hi SirJB7,


Thanks for your response....Marco is in line what is needed but not working perfectly....


Some time it just miss the one line (matching) while some time it just run downwards from the cell which is selected. Say i have selected B10 and above B10 there are few line with matching criteria which should be removed....but it do not remove them and just start removing the matching row downwards.


Regards,

Kuldeep

 

[SirJB7]

Hi, kuldeepjainesl!

Would you upload the sample file?

Regards!

 

[kuldeepjainesl]

Hi SirJB7,


Please find the dummy data...at http://sdrv.ms/Mjlsvq


Check invalid data sheet...i have tried to filter data from coloum 2 with value of 9. All data in this sheet is OK.

Now check output sheet and check for any value as 9 (may check via auto filter) and you will see that there are two more rows with "9" value which should have been copied to invalid sheet.


Regards,

Kuldeep

 

[SirJB7]

Hi, kuldeepjainesl!


Running the macro with the downloaded file, it deleted (moved) 2 rows containing 9 in column B:

[pre]

94	9
411	9
Running it again, it didn't delete any row.


Using sheet "Dummy Data" as "Output", running it deleted 946 rows over a total of 1036 containing 9. Running it again, it deleted 88. Running it again, 2. And once more, it didn't delete any row.


It an issue with pairs of adjacent deletable rows. Replace the sentences within the Do...loop structure by this:

-----

If .Cells(I, iTestColumn).Value = "" Or _
.Cells(I, iTestColumn).Value = sTestValue Then
J = J + 1
.Cells(I, 1).EntireRow.Copy Worksheets("Invalid data").Cells(J, 1)
.Cells(I, 1).EntireRow.Delete
Else
I = I + 1
End If

[/pre]
-----


Check now if it's working properly.


Regards!

 

[kuldeepjainesl]

Hi SirJB7,


I could do what you expected from me to change in code and the results is "Hummm...."its working dude"


Thanks a lot....


Regards,

Kuldeep

 

[SirJB7]

Hi, Kuldeep!

Glad you solved it. Thanks for your feedback and for your kind words too. Welcome back whenever needed or wanted.

Regards!