• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

need a vba code for solve a query

mahaveer

mahaveer

Member
seLECT SHEET 1

Select K1:N100

Press Ctrl+F

Click on Option>Select Values from Look in Combo>Find All>Press Ctrl+A

Close Find and Replace Dialog box

Now press Delete key


BECAUSE I HAVE SOME CELLS FROM COLUMN K1 TO COLUMN N100 WHICH HAVE INCLUDE IF FORMULA BUT THEIR RESULTS IS "" (BLANK)..... SO I WANT TO DELETE THEM......THROUGH VBA ONLY..

THEREFORE I NEED A VBA CODE FOR THE ABOVE 6 LINES ONLY?
 
Hi Mahaveer,


Try this:


Sub DelBlanks()


Dim rng As Range

Set rng = ActiveSheet.UsedRange


' Check each cell in the range and if its value is empty, delete the row

For Each Cell In rng

If Cell.Value = "" Then

Cell.EntireRow.Delete

End If

Next Cell


End Sub


In case it does not meet your requirement, please consider uploading your sample workbook.


Kaushik
 
No nazmul_muneer

this attachment is not related to my problem..

but it is my real problem

i want to make this type file.......


therefore previously i was using if formula to get the correct value from the column A and B.....

but there i was getting some blanks cell too.....

can u make this file for me....

or

can u make a code to delete cells which have if formula but having no value.......
 
Hi Mahaveer,


What I understood from your file is, through userform you are adding the item name and category type; and the code is deleting the duplicate and sorting the column in ascending order.


Now you want to fetch the input details in Col F to Col N as you have shown in the highlighted region.Since, your code is already taken care of the sorting rule, I believe you do not need to do the same when you are fetching the data in Col F to Col N.


I also understood that, there can not be same items with multiple categories as you are deleting the duplicates(by code) from Col A


I have written an array formula(using two dynamic named ranges) to fetch the data from col F to Col N (Per user inputs through user form which you have designed). I have dragged the formula till the highlighted region. You can drag it as much as you want.


http://speedy.sh/Cmg4b/automatic-work-1-Mahaveer.xlsm


Try to enter some new item and any one of the nine categories through user form, and test the data from Col F to Col N.


Check it and let me know if it is fine, then we will talk about the explanation, if required.


Regards,

Kaushik
 
kaushik my bro you are really genious dude.........


i dont know how to say u thanks............


"THANK YOU"..........VERY MUCH BRO"
 
You must log in or register to reply here.
Back
Top