Code/VBA pls

[Vishaal]

We have the following sheet

Excel 2010 32 bit

	A​	B​	C​	D​	E​	F​
1​	1​	224​	205​	218​	230​	218​
2​	2​	112​	106​	100​	94​	105​
3​	3​	51​	58​	59​	46​	49​
4​	4​	22​	24​	22​	27​	27​
5​	5​	11​	12​	12​	12​	9​
6​	6​	8​	10​	10​	8​	12​
7​	7​	2​	2​	2​	0​	1​
8​	8​	0​	3​	1​	3​	3​
9​	9​	1​	​	​	1​	​
10​	10​	​	​	​	2​	​
11​	11​	​	​	​	​	​
12​	12​	​	​	​	​	​
13​	13​	​	​	​	​	​
14​	14​	​	​	​	​	​
15​	15​	​	​	​	​	​
16​	16​	​	​	​	​	​

Sheet: Sheet1

now we want to check the cells A1:F16, which coloumn was maximum entry (i mean which cell was filled more then others), after finding maximum filled cell it will remove the data from that cells in cell A

Result are
Excel 2010 32 bit

	A​	B​	C​	D​	E​	F​
1​	1​	224​	205​	218​	230​	218​
2​	2​	112​	106​	100​	94​	105​
3​	3​	51​	58​	59​	46​	49​
4​	4​	22​	24​	22​	27​	27​
5​	5​	11​	12​	12​	12​	9​
6​	6​	8​	10​	10​	8​	12​
7​	7​	2​	2​	2​	0​	1​
8​	8​	0​	3​	1​	3​	3​
9​	9​	1​	​	​	1​	​
10​	10​	​	​	​	2​	​

Sheet: Sheet1

help pls

 

[alansidman]

Option Explicit


Sub clearextra()
    Dim i As Long, lr As Long, lc As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To lr
        lc = Cells(i, Columns.Count).End(xlToLeft).Column
        If lc = 1 Then
            Range("A" & i).EntireRow.ClearContents
        End If
    Next i


End Sub

 

[Vishaal]

Thanks alansidman Sir Ji,

its wroking, thanks for your quick and easy solution

 

[Vishaal]

I dont know why but sometimes its not working

 

[JoeMo]

I dont know why but sometimes its not working

Post an example of your starting data when its not working.

 

[Vishaal]

pls check

Excel 2010 32 bit

	A​	B​	C​	D​	E​
1​	1​	102​	123​	117​	101​
2​	2​	71​	68​	70​	66​
3​	3​	56​	55​	54​	47​
4​	4​	32​	34​	26​	33​
5​	5​	26​	25​	30​	27​
6​	6​	21​	20​	19​	25​
7​	7​	12​	13​	11​	9​
8​	8​	9​	7​	13​	9​
9​	9​	3​	6​	5​	4​
10​	10​	4​	3​	3​	8​
11​	11​	4​	3​	3​	1​
12​	12​	3​	3​	4​	2​
13​	13​	1​	2​	0​	3​
14​	14​	0​	0​	1​	1​
15​	15​	0​	2​	1​	1​
16​	16​	2​	1​	0​	0​
17​	17​	0​	​	1​	1​
18​	18​	0​	​	​	1​
19​	19​	0​	​	​	0​
20​	20​	0​	​	​	1​
21​	21​	0​	​	​	​
22​	22​	1​	​	​	​
23​	23​	​	​	​	​
24​	24​	​	​	​	​
25​	25​	​	​	​	​
26​	26​	​	​	​	​
27​	27​	​	​	​	​
28​	28​	​	​	​	​
29​	29​	​	​	​	​
30​	30​	​	​	​	​
31​	31​	​	​	​	​
32​	32​	​	​	​	​
33​	33​	​	​	​	​
34​	34​	​	​	​	​
35​	35​	​	​	​	​
36​	36​	​	​	​	​

Sheet: Sheet1

 

[JoeMo]

The code from post #2 works fine for me with the data from post #6 . Note that the code from post #2 assumes the count of filled cells will always be largest for col A. If you run it on a data set where one or more of the cols B:E have data down to or beyond the last filled cell in col A, nothing will happen.

Your OP said: "now we want to check the cells A1:F16, which coloumn was maximum entry (i mean which cell was filled more then others), after finding maximum filled cell it will remove the data from that cells in cell A". That only makes sense if col A has the most filled cells.

Can you explain what you want in more detail? Will col A always have more filled cells? If not, what do you want to happen?

 

[Vishaal]

you are right, every time column A have more filled cells

suppose we have 30 Filled cells in column A, now code will check B1:M25 and in maximum filled cell in B1:M25 is D16 then it will remove all the filled cells from A after 16

 

[JoeMo]

you are right, every time column A have more filled cells

suppose we have 30 Filled cells in column A, now code will check B1:M25 and in maximum filled cell in B1:M25 is D16 then it will remove all the filled cells from A after 16

Then I think the code from post#2 should be working. If you run that code on the data you posted in post#6, what exactly doesn't work? Is there any change to the data after the code is run?

 

[Vishaal]

i dont think so, when we run the code, nothing will work