Code/VBA pls

Vishaal · Oct 29, 2019

We have the following sheet

Excel 2010 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A

[/th][th]

B

[/th][th]

C

[/th][th]

D

[/th][th]

E

[/th][th]

F

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1

[/td][td]

1

[/td][td]

224

[/td][td]

205

[/td][td]

218

[/td][td]

230

[/td][td]

218

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2

[/td][td]

2

[/td][td]

112

[/td][td]

106

[/td][td]

100

[/td][td]

94

[/td][td]

105

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3

[/td][td]

3

[/td][td]

51

[/td][td]

58

[/td][td]

59

[/td][td]

46

[/td][td]

49

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4

[/td][td]

4

[/td][td]

22

[/td][td]

24

[/td][td]

22

[/td][td]

27

[/td][td]

27

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5

[/td][td]

5

[/td][td]

11

[/td][td]

12

[/td][td]

12

[/td][td]

12

[/td][td]

9

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6

[/td][td]

6

[/td][td]

8

[/td][td]

10

[/td][td]

10

[/td][td]

8

[/td][td]

12

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7

[/td][td]

7

[/td][td]

2

[/td][td]

2

[/td][td]

2

[/td][td]

0

[/td][td]

1

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8

[/td][td]

8

[/td][td]

0

[/td][td]

3

[/td][td]

1

[/td][td]

3

[/td][td]

3

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9

[/td][td]

9

[/td][td]

1

[/td][td]

1

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10

[/td][td]

10

[/td][td]

2

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11

[/td][td]

11

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12

[/td][td]

12

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13

[/td][td]

13

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

14

[/td][td]

14

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

15

[/td][td]

15

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

16

[/td][td]

16

[/td][td]

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

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
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A

[/th][th]

B

[/th][th]

C

[/th][th]

D

[/th][th]

E

[/th][th]

F

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1

[/td][td]

1

[/td][td]

224

[/td][td]

205

[/td][td]

218

[/td][td]

230

[/td][td]

218

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2

[/td][td]

2

[/td][td]

112

[/td][td]

106

[/td][td]

100

[/td][td]

94

[/td][td]

105

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3

[/td][td]

3

[/td][td]

51

[/td][td]

58

[/td][td]

59

[/td][td]

46

[/td][td]

49

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4

[/td][td]

4

[/td][td]

22

[/td][td]

24

[/td][td]

22

[/td][td]

27

[/td][td]

27

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5

[/td][td]

5

[/td][td]

11

[/td][td]

12

[/td][td]

12

[/td][td]

12

[/td][td]

9

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6

[/td][td]

6

[/td][td]

8

[/td][td]

10

[/td][td]

10

[/td][td]

8

[/td][td]

12

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7

[/td][td]

7

[/td][td]

2

[/td][td]

2

[/td][td]

2

[/td][td]

0

[/td][td]

1

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8

[/td][td]

8

[/td][td]

0

[/td][td]

3

[/td][td]

1

[/td][td]

3

[/td][td]

3

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9

[/td][td]

9

[/td][td]

1

[/td][td]

1

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10

[/td][td]

10

[/td][td]

2

[/td][td]

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

help pls

alansidman · Oct 29, 2019

Code:

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 · Oct 29, 2019

Thanks alansidman Sir Ji,

its wroking, thanks for your quick and easy solution

Vishaal · Oct 29, 2019

I dont know why but sometimes its not working

JoeMo · Oct 29, 2019

Vishaal said:
I dont know why but sometimes its not working

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

Vishaal · Oct 29, 2019

pls check

Excel 2010 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A

[/th][th]

B

[/th][th]

C

[/th][th]

D

[/th][th]

E

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1

[/td][td]

1

[/td][td]

102

[/td][td]

123

[/td][td]

117

[/td][td]

101

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2

[/td][td]

2

[/td][td]

71

[/td][td]

68

[/td][td]

70

[/td][td]

66

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3

[/td][td]

3

[/td][td]

56

[/td][td]

55

[/td][td]

54

[/td][td]

47

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4

[/td][td]

4

[/td][td]

32

[/td][td]

34

[/td][td]

26

[/td][td]

33

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5

[/td][td]

5

[/td][td]

26

[/td][td]

25

[/td][td]

30

[/td][td]

27

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6

[/td][td]

6

[/td][td]

21

[/td][td]

20

[/td][td]

19

[/td][td]

25

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7

[/td][td]

7

[/td][td]

12

[/td][td]

13

[/td][td]

11

[/td][td]

9

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8

[/td][td]

8

[/td][td]

9

[/td][td]

7

[/td][td]

13

[/td][td]

9

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9

[/td][td]

9

[/td][td]

3

[/td][td]

6

[/td][td]

5

[/td][td]

4

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10

[/td][td]

10

[/td][td]

4

[/td][td]

3

[/td][td]

3

[/td][td]

8

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11

[/td][td]

11

[/td][td]

4

[/td][td]

3

[/td][td]

3

[/td][td]

1

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12

[/td][td]

12

[/td][td]

3

[/td][td]

3

[/td][td]

4

[/td][td]

2

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13

[/td][td]

13

[/td][td]

1

[/td][td]

2

[/td][td]

0

[/td][td]

3

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

14

[/td][td]

14

[/td][td]

0

[/td][td]

0

[/td][td]

1

[/td][td]

1

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

15

[/td][td]

15

[/td][td]

0

[/td][td]

2

[/td][td]

1

[/td][td]

1

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

16

[/td][td]

16

[/td][td]

2

[/td][td]

1

[/td][td]

0

[/td][td]

0

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

17

[/td][td]

17

[/td][td]

0

[/td][td]

1

[/td][td]

1

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

18

[/td][td]

18

[/td][td]

0

[/td][td]

1

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

19

[/td][td]

19

[/td][td]

0

[/td][td]

0

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

20

[/td][td]

20

[/td][td]

0

[/td][td]

1

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

21

[/td][td]

21

[/td][td]

0

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

22

[/td][td]

22

[/td][td]

1

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

23

[/td][td]

23

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

24

[/td][td]

24

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

25

[/td][td]

25

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

26

[/td][td]

26

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

27

[/td][td]

27

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

28

[/td][td]

28

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

29

[/td][td]

29

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

30

[/td][td]

30

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

31

[/td][td]

31

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

32

[/td][td]

32

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

33

[/td][td]

33

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

34

[/td][td]

34

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

35

[/td][td]

35

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

36

[/td][td]

36

[/td][td]

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

JoeMo · Oct 29, 2019

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 · Oct 29, 2019

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 · Oct 29, 2019

Vishaal said:
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 · Oct 29, 2019

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

Code/VBA pls

Vishaal

Well-known Member

Excel Facts

alansidman

Well-known Member

Vishaal

Well-known Member

Vishaal

Well-known Member

JoeMo

MrExcel MVP

Vishaal

Well-known Member

JoeMo

MrExcel MVP

Vishaal

Well-known Member

JoeMo

MrExcel MVP

Vishaal

Well-known Member

Similar threads

Forum statistics

Share this page

Code/VBA pls

Well-known Member

Excel Facts

Well-known Member

Well-known Member

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock