Clear data in a cell if the other cells are blank

[suryasabniveesu]

There is some data in the range A1:A100.Now I want to delete the data in those cells against which there will be data in range B1:B100 and C1:C100


To be specific about my requirement,I got a worksheet which contains data in A2,B2,C2, A10,B10,C10 and so on.Now I want the data in column A automatically to be deleted IF corresponding cells in B and C columns contain data.

 

[Hui]

Suryasabniveesu


Firstly, Welcome to the Chandoo.org forums.


Copy the code to the Worksheet Object in VBA


If you are unfamiliar with VBA

Alt F11

Double click on the Worksheet you want to apply this to

Paste the code in the right Hand pane

[pre]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row <= 100 And (Target.Column = 2 Or Target.Column = 3) Then
Cells(Target.Row, 1) = ""
End If
End Sub

[/pre]

You will now have to save the file as a Excel Macro file type *.xlsm or Excel Binary file type *.xlsb

 

[suryasabniveesu]

Thank you very much. Thanks a lot for your response.


Yes I am unfamiliar with VBA.But I tried your code,struggled a bit to run the code and to know how it works and finally it is working. But it solved half of my problem.It works for me when I manually enter data in Column B & C


But What i have to do when I receive such type of file from others.My requirement is when I received such type of data,I want to delete data in Column A in only those cells where against those cells there will be data in Column B and C.


I hope ur logic is absolutely working but little bit modification is required in code which I dont know how to do :-(

 

[SirJB7]

Hi, suryasabniveesu!


The code provided by Hui works in the Change event of the worksheet, that's to say when you modify any cell value, specifically in this case in columns B and C.


If you receive a file from others, you should include the same code in that file, and then perform an update operation to trigger the event. You can select and copy cells pasting on same range.


Regards!

 

[suryasabniveesu]

How can I upload excel file to make it more clear?


@SirJB7 - Thanks for the hint.


the origin for this requirement is I have downloaded a macro for deleting blank rows in a range.It's working fine.But if any cell doesn't contain data,and cells to the right or left are containing data then that particular row is not being deleted. So I want to make those cells as blank where corresponding cells, either left or right (as per our input),do not contain data.


Oops realised my mistake.Sorry.there is opposite requirement as per my intial question.


If B2="" and C2="" then make A2 as BLANK. This is it...this is my problem.then i can run deleteblankrows macro successfully.

 

[Hui]

Suryasabniveesu


Can you post the code and highlight where you want to delete the row?

 

[suryasabniveesu]

Code for deleting blank rows:


Sub DeleteBlankRows1()


'Deletes the entire row within the selection if the ENTIRE row contains no data.


'We use Long in case they have over 32,767 rows selected.


Dim i As Long


'We turn off calculation and screenupdating to speed up the macro.


With Application


.Calculation = xlCalculationManual


.ScreenUpdating = False


'We work backwards because we are deleting rows.


For i = Selection.Rows.Count To 1 Step -1


If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then


Selection.Rows(i).EntireRow.Delete


End If


Next i


.Calculation = xlCalculationAutomatic


.ScreenUpdating = True


End With


End Sub

 

[Hui]

Suryasabniveesu

Can you now tell me what this code does and what you want it to do?

 

[suryasabniveesu]

This code deletes all blank rows in a sheet.I dont have any problem with this code and I have been using it for a while. If any row is having data even in a single cell, then the row cant be deleted.If the given range is big, from which I have to delete unnecessary data from cells, I am doing it manually.


Once I can clear the data from those cells and make them as blank,then I will run the above code to compact my data range.

 

[suryasabniveesu]

Can anybody give answer to this??

 

[Hui]

Suryasabniveesu


I am confused


You say this code works ok


But if a single row has any data it can't be deleted


Do you want to delete all the selected Rows or only the rows without any data ?


Please clarify what you want to do

 

[SirJB7]

Hi, suryasabniveesu!

I agree with Hui, please elaborate it a bit more and clarify your question. From your last two posts, I don't understand which is the actual question, if there is one.

Regards!