need all in the same row if we have a letter

[sksanjeev786]

Hi Team

I have data inwhich for few cell due to letter is it comming in 2 line but i need i same cell so that i can consider in same row....

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
6	Unaided Brand Awareness - First mention	45	51	37	74	23	34	65	14	26	45	-6
7						C			C
8	Unaided Brand Awareness - Any mention	45	60	39	78	18	39	75	15	37	64	4
9	Aided Brand Awareness	45	100	50	100	0	49	94	-6	57	98	-2
10						B
11	Brand Favorability	45	87	42	84	-3	42	81	-6	50	86	0
12	Purchase Intent	45	87	38	76	-11	40	77	-10	50	86	0
13	Brand Affinity	45	69	25	50	-19	32	62	-7	44	76	7
14												A
Sheet2

 

[MARK858]

Assuming your cells aren't merged then try the code below (although you haven't stated what you want the result to look like)

Sub sksanjeev786()
   Dim lr As Long, i As Long
   Application.ScreenUpdating = False
   
    lr = Cells(Rows.Count, "A").End(xlUp).Row
  
   For i = lr To 6 Step -1
        If Left(Cells(i, "F"), 1) Like "[A-Za-z]" Then
            Cells(i, "F").Offset(-1).Value = Cells(i, "F").Offset(-1).Value & Cells(i, "F").Value
            Cells(i, "F").EntireRow.Delete
        End If
    Next

End Sub

 

[sksanjeev786]

Assuming your cells aren't merged then try the code below (although you haven't stated what you want the result to look like)

Sub sksanjeev786()
   Dim lr As Long, i As Long
   Application.ScreenUpdating = False
  
    lr = Cells(Rows.Count, "A").End(xlUp).Row
 
   For i = lr To 6 Step -1
        If Left(Cells(i, "F"), 1) Like "[A-Za-z]" Then
            Cells(i, "F").Offset(-1).Value = Cells(i, "F").Offset(-1).Value & Cells(i, "F").Value
            Cells(i, "F").EntireRow.Delete
        End If
    Next

End Sub

Hey Mark,

Thanks for your time on this.

I have tried with above Macro and not able to see any changes after appyig same on above data.

Regards
Sanjeev

 

[MARK858]

When I run the the code it changes from

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
6	Unaided Brand Awareness - First mention	45	51	37	74	23	34	65	14	26	45	-6
7						C			C
8	Unaided Brand Awareness - Any mention	45	60	39	78	18	39	75	15	37	64	4
9	Aided Brand Awareness	45	100	50	100	0	49	94	-6	57	98	-2
10						B
11	Brand Favorability	45	87	42	84	-3	42	81	-6	50	86	0
12	Purchase Intent	45	87	38	76	-11	40	77	-10	50	86	0
13	Brand Affinity	45	69	25	50	-19	32	62	-7	44	76	7
14												A
Sheet2

to

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
6	Unaided Brand Awareness - First mention	45	51	37	74	23C	34	65	14	26	45	-6
7	Unaided Brand Awareness - Any mention	45	60	39	78	18	39	75	15	37	64	4
8	Aided Brand Awareness	45	100	50	100	0B	49	94	-6	57	98	-2
9	Brand Favorability	45	87	42	84	-3	42	81	-6	50	86	0
10	Purchase Intent	45	87	38	76	-11	40	77	-10	50	86	0
11	Brand Affinity	45	69	25	50	-19	32	62	-7	44	76	7
12												A
Sheet2

Note column F

Although I now see you have letters in other columns further along and so I will have to recode for this

 

[MARK858]

Code below adjusted for more columns and multiple entries in the columns

Sub sksanjeev786B()
    Dim lr As Long, i As Long, j As Integer, k As Integer
    Application.ScreenUpdating = False
   
    lr = Cells(Rows.Count, "A").End(xlUp).Row
   
    For j = 2 To 12
       
        For i = lr To 6 Step -1
           
            If Left(Cells(i, j), 1) Like "[A-Za-z]" Then
                For k = 2 To 12
                    If Left(Cells(i, k), 1) Like "[A-Za-z]" Then
                        Cells(i, k).Offset(-1).Value = Cells(i, k).Offset(-1).Value & Cells(i, k).Value
                    End If
                Next
                Cells(i, j).EntireRow.Delete
            End If
       
        Next
   
    Next
End Sub

For me changes

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
6	Unaided Brand Awareness - First mention	45	51	37	74	23	34	65	14	26	45	-6
7						C			C
8	Unaided Brand Awareness - Any mention	45	60	39	78	18	39	75	15	37	64	4
9	Aided Brand Awareness	45	100	50	100	0	49	94	-6	57	98	-2
10						B
11	Brand Favorability	45	87	42	84	-3	42	81	-6	50	86	0
12	Purchase Intent	45	87	38	76	-11	40	77	-10	50	86	0
13	Brand Affinity	45	69	25	50	-19	32	62	-7	44	76	7
14												A
Sheet3

to

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
6	Unaided Brand Awareness - First mention	45	51	37	74	23C	34	65	14C	26	45	-6
7	Unaided Brand Awareness - Any mention	45	60	39	78	18	39	75	15	37	64	4
8	Aided Brand Awareness	45	100	50	100	0B	49	94	-6	57	98	-2
9	Brand Favorability	45	87	42	84	-3	42	81	-6	50	86	0
10	Purchase Intent	45	87	38	76	-11	40	77	-10	50	86	0
11	Brand Affinity	45	69	25	50	-19	32	62	-7	44	76	7A
12
13
14
Sheet3

 

[sksanjeev786]

Code below adjusted for more columns and multiple entries in the columns

Sub sksanjeev786B()
    Dim lr As Long, i As Long, j As Integer, k As Integer
    Application.ScreenUpdating = False
  
    lr = Cells(Rows.Count, "A").End(xlUp).Row
  
    For j = 2 To 12
      
        For i = lr To 6 Step -1
          
            If Left(Cells(i, j), 1) Like "[A-Za-z]" Then
                For k = 2 To 12
                    If Left(Cells(i, k), 1) Like "[A-Za-z]" Then
                        Cells(i, k).Offset(-1).Value = Cells(i, k).Offset(-1).Value & Cells(i, k).Value
                    End If
                Next
                Cells(i, j).EntireRow.Delete
            End If
      
        Next
  
    Next
End Sub

For me changes

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
6	Unaided Brand Awareness - First mention	45	51	37	74	23	34	65	14	26	45	-6
7						C			C
8	Unaided Brand Awareness - Any mention	45	60	39	78	18	39	75	15	37	64	4
9	Aided Brand Awareness	45	100	50	100	0	49	94	-6	57	98	-2
10						B
11	Brand Favorability	45	87	42	84	-3	42	81	-6	50	86	0
12	Purchase Intent	45	87	38	76	-11	40	77	-10	50	86	0
13	Brand Affinity	45	69	25	50	-19	32	62	-7	44	76	7
14												A
Sheet3

to

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
6	Unaided Brand Awareness - First mention	45	51	37	74	23C	34	65	14C	26	45	-6
7	Unaided Brand Awareness - Any mention	45	60	39	78	18	39	75	15	37	64	4
8	Aided Brand Awareness	45	100	50	100	0B	49	94	-6	57	98	-2
9	Brand Favorability	45	87	42	84	-3	42	81	-6	50	86	0
10	Purchase Intent	45	87	38	76	-11	40	77	-10	50	86	0
11	Brand Affinity	45	69	25	50	-19	32	62	-7	44	76	7A
12
13
14
Sheet3

Thank you so much!! for your help with this.


Regards
Sanjeev

 

[MARK858]

You're welcome