• 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.

REMOVE ALL BLANKS SOME DATA CONTAINING ....................

B

basavarajkh

New Member
HI


I HAD SOME DATA IN COLUMN “A” WANTS TO REMOVE ALL THE BLANKS & THE DATA WHICH IS MENTIONED BELOW;


SMS

Category: Real Estate Management

All Listings | SMS

Click to view larger map

Click to enlarge

0 Review(s) Rate it


WITH THE HELP OF VBA CODE OR SOME FORMULA.


PLEASE FIND THE ATTACHED FILE.

http://speedy.sh/HYa7V/Test.xls


THANKS


REGARDS

BASAVARAJ K H
 
Hi Basavaraj ,


I am copying and pasting the first few rows from your file ; with reference to this sample , can you say which rows should appear , and which should not appear ?

[pre]
Code: 
Dove Management Services Pte Ltd
Blk 664 Woodlands Ring Rd #07-198 Singapore 730664
Tel: 6891 3496   

Category: Real Estate Management
All Listings | SMS

Click to view larger map
Click to enlarge
0 Review(s) Rate it

482

Dra Property Management Pte Ltd
35 Desker Rd Singapore 209567
Tel: 6296 7087   

Category: Real Estate
All Listings | SMS  

Click to view larger map
Click to enlarge
0 Review(s) Rate it

483
Let me ask ; should the final output be as shown below ?

Dove Management Services Pte Ltd
Blk 664 Woodlands Ring Rd #07-198 Singapore 730664
Tel: 6891 3496
482
Dra Property Management Pte Ltd
35 Desker Rd Singapore 209567
Tel: 6296 7087
483
[/pre]
Narayan
 
Hi Basavaraj,


You can follow the following four steps to do the same:


Step1:

A.Run the following macro to delete the blank cells:

Sub delblanks()

Dim i As Integer

For i = 1 To 265

If Range("A" & (i)).Value = "" Then

Range("A" & (i)).Select

Selection.EntireRow.Delete

End If

Next i

End Sub


However you can excecute this step without using macro as well. To do this, you need to press Ctrl+G from your key board,the "Go To" dialog box will open, then you need to click the button "Special", select "Blanks" from "Go To Special" dialog box and hit Ok, then you will get to see that the blank rows are highlighted, then you need to press "Ctrl+ 'minus(-)' sign from your key board, the "delete" dialog box will open, then you need to select entire row option and hit "ok", the blank rows will be deleted.


But, by both of these techniques, you will see that some of the blank rows still remain there.The reason being, each of those rows contain double spaces in them which you need to tackle by following the below mentioned step.


Step2:

i) select the entire column "A"

ii)Hit Ctrl+H from your key board to open the 'Find and Replace' dialog box

III)Place the cursor in 'Find What' part and prees 'space bar' for two times from your key board; and then place the cursor in 'Replace with' part and hit the 'replace all' button. Then you will get to see that excel will make 57 replacements for you (according to your data).

Note: In this way you are basically, removing double spaces from 57 remaining blank rows.


Step 3:

Again run the same macro (or follow the excel based process by going to "Go To Special" dialog box) to delete the remaining blank rows


Step4:

Finally,you filter column A, select your desired items(which want to delete, hit Ok button. Then again go to "Go to Special" dialog box, select "Visible Cells Only", hit ok,then you need to press "Ctrl+ 'minus(-)' sign from your key board (as you did earlier), the "delete" dialog box will open, then you need to select entire row and hit "ok", the selected items will be deleted. Finally remove the filter to check everything is fine.(In case you find any further bank rows, just run the same macro once again to take care of it)


The entire process may sounds little complecated. But once you follow the steps, you can finish it off within 3 to 4 minutes.


Let me knkow if you need any further help.


Regrds,

Kaushik
 
Thanks Narayan & Kaushik


Sorry Kaushik your solution works delete the blanks cells only

I want to delete blank cells and the data which contains as follows.in column A.


(1) "SMS"

(2) "Category: Real Estate Management"

(3) "All Listings | SMS"

(4) "Click to view larger map"

(5) "Click to enlarge"

(6) "0 Review(s) Rate it"


Regards

Basavaraj K H
 
That can be done if you conduct step 4 right (filter the selected items and delete them), if, I am not wrong! Please confirm.

Or, perhaps, I did not get your point.


Waiting for your reply.


Kaushik
 
So, did you get the solution now?


Let me know if I was not able to give you the right solution of your problem.


Looking forward to your reply.


Regards,

Kaushik
 
I know 4 steps (technique's),

but I received more than 12 times in 8 Hours.

So I planned to prepare VBA Code.

Please help me to write VBA Code.

It's help me to finish work early.
 
Hi Basavaraj ,


Try the following code , in the sheet section of your VBA Project :

[pre]
Code: 
Public Sub Delete_Specified_Rows()
Dim Data_Range As Range
Set Data_Range = Range("A3:A265")
Data_Range.Select
number_of_rows = Selection.Rows.Count
I = 0
J = 0
Do
cell = Data_Range.Cells(1, 1).Offset(I, 0).Value
On Error Resume Next
f1 = False
f2 = False
f3 = False
f4 = False
f5 = False
f1 = Len(Trim(cell)) = 0
f2 = Application.WorksheetFunction.Find("Category", cell) > 0
f3 = Application.WorksheetFunction.Find("SMS", cell) > 0
f4 = Application.WorksheetFunction.Find("Click", cell) > 0
f5 = Application.WorksheetFunction.Find("Review", cell) > 0
On Error GoTo 0
If f1 Or f2 Or f3 Or f4 Or f5 Then
Data_Range.Cells(1, 1).Offset(I, 0).EntireRow.Delete
J = J + 1
Else
I = I + 1
End If
Loop Until (I + J) >= number_of_rows
End Sub
[/pre]
Narayan
 
You must log in or register to reply here.
Back
Top