Excel - Combine Line One with Line Two

[RudeBoy]

When I run the Macro it deletes all the data here is my data
It was working last week but this time it is deleting everything but the first two lines?
Am using this code:
Sub combineRows()
Dim ws As Worksheet, r As Long, lr As Long, cell As Range
Set ws = ActiveSheet
lr = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
Application.ScreenUpdating = False
For r = lr To 3 Step -1
Set cell = ws.Cells(r, "A")
If cell = "" Then
cell.Offset(-1, 2) = cell.Offset(, 1)
cell.EntireRow.Delete
End If
Next r
End Sub

Sheet1
	A	B
Incidents Project - Jobs Converted-k.xlsm
1		EDIBCBD9
2		228 //BADRC EXEC EDIP0022,JOBNAME=EDIBCBD9,
3		EDIBCBM9
4		316 //BADRC EXEC EDIP0022,JOBNAME=EDIBCBM9,
5		EDIBCBW9
6		316 //BADRC EXEC EDIP0022,JOBNAME=EDIBCBW9,
7		EDIBCBY9
8		315 //BADRC EXEC EDIP0022,JOBNAME=EDIBCBY9,
9		EDIBC601
10		83 //BADRC EXEC EDIP0022,JOBNAME=EDIBC601,
11		EDIBC604
12		959 //BADRC EXEC EDIP0022,JOBNAME=EDIBC604,
13		EDIBC605
14		64 //BADRC EXEC EDIP0022,JOBNAME=EDIBC605,
15		EDIBC606
16		145 //BADRC EXEC EDIP0022,JOBNAME=EDIBC606,
17		EDIBC607
18		103 //BADRC EXEC EDIP0022,JOBNAME=EDIBC607,
19		EDIBC608
20		357 //BADRC EXEC EDIP0022,JOBNAME=EDIBC608,
21		EDIBC609
22		42 //BADRC EXEC EDIP0022,JOBNAME=EDIBC609,
23		EDIBC611
24		58 //BADRC EXEC EDIP0022,JOBNAME=EDIBC611,

Cell	Condition	Cell Format	Stop If True
Cells with Conditional Formatting
A:A	Cell Value	="N"	text	NO
A:A	Cell Value	="Y"	text	NO

 

[JLGWhiz]

There is nothing basically wrong with the code. The problem is likely that the code was being run on a sheet with many blank cells in column A. The criteria to delete is if column A is blank.

 

[Rick Rothstein]

You have this loop...

For r = lr To 3 Step -1

which stops at Row 3... maybe that is why Rows 1 and 2 are not processed. However, I do not think you can just change the 3 to 1 because you have this inside the loop...

cell.Offset(-1, 2) = cell.Offset(, 1)

which is offsetting from the cell at Row "r" by negative one which would take it off the worksheet and raise an error when "r" reaches 1.

 

[RudeBoy]

You have this loop...

For r = lr To 3 Step -1

which stops at Row 3... maybe that is why Rows 1 and 2 are not processed. However, I do not think you can just change the 3 to 1 because you have this inside the loop...

cell.Offset(-1, 2) = cell.Offset(, 1)

which is offsetting from the cell at Row "r" by negative one which would take it off the worksheet and raise an error when "r" reaches 1.

The End Result I am looking for is this:

Incidents Project - Jobs Converted-l.xlsm
	A	B	C	D	E	F	G	H
1	n	EDIBCBD9	228 //BADRC EXEC EDIP0022,JOBNAME=EDIBCBD9,
2	n	EDIBCBM9	316 //BADRC EXEC EDIP0022,JOBNAME=EDIBCBM9,
3	n	EDIBCBW9	316 //BADRC EXEC EDIP0022,JOBNAME=EDIBCBW9,
4	n	EDIBCBY9	315 //BADRC EXEC EDIP0022,JOBNAME=EDIBCBY9,
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A:A	Cell Value	="N"	text	NO
A:A	Cell Value	="Y"	text	NO

 

[RudeBoy]

There is nothing basically wrong with the code. The problem is likely that the code was being run on a sheet with many blank cells in column A. The criteria to delete is if column A is blank.

The End Result I am looking for is this:

Sheet1
	A	B	C	D	E	F	G	H
Incidents Project - Jobs Converted-l.xlsm
1	n	EDIBCBD9	228 //BADRC EXEC EDIP0022,JOBNAME=EDIBCBD9,
2	n	EDIBCBM9	316 //BADRC EXEC EDIP0022,JOBNAME=EDIBCBM9,
3	n	EDIBCBW9	316 //BADRC EXEC EDIP0022,JOBNAME=EDIBCBW9,
4	n	EDIBCBY9	315 //BADRC EXEC EDIP0022,JOBNAME=EDIBCBY9,

Cell	Condition	Cell Format	Stop If True
Cells with Conditional Formatting
A:A	Cell Value	="N"	text	NO
A:A	Cell Value	="Y"	text	NO

 

[JLGWhiz]

In the OP the worksheet image posted shows column A with blank cells. If comlumn A has blank cells the code will delete every row on that sheet that does not have a value in column A, except roiws 1 and 2, as Rick pointed out. I ran the code on my system and as long as there is data in column a of a row, it will not delete that row, but it does delete the rows where columna A is blank. So, I suggest you try to run the code on a sheet where column A has data and see if it still deletes all the rows except the first two.

 

[RudeBoy]

In the OP the worksheet image posted shows column A with blank cells. If comlumn A has blank cells the code will delete every row on that sheet that does not have a value in column A, except roiws 1 and 2, as Rick pointed out. I ran the code on my system and as long as there is data in column a of a row, it will not delete that row, but it does delete the rows where columna A is blank. So, I suggest you try to run the code on a sheet where column A has data and see if it still deletes all the rows except the first two.

When I have data in column A nothing happens I wonder if it is the format of the file as the excel is a .xlsm file?

 

[RudeBoy]

Well Lets Start over = I have this data:

Incidents Project - Jobs Converted-l.xlsm
	A	B
6	n	EDIBC601
7	n	83 //BADRC EXEC EDIP0022,JOBNAME=EDIBC601,
8	n	EDIBC604
9	n	959 //BADRC EXEC EDIP0022,JOBNAME=EDIBC604,
10	n	EDIBC605
11	n	64 //BADRC EXEC EDIP0022,JOBNAME=EDIBC605,
12	n	EDIBC606
13	n	145 //BADRC EXEC EDIP0022,JOBNAME=EDIBC606,
14	n	EDIBC607
15	n	103 //BADRC EXEC EDIP0022,JOBNAME=EDIBC607,
16	n	EDIBC608
17	n	357 //BADRC EXEC EDIP0022,JOBNAME=EDIBC608,
18	n	EDIBC609
19	n	42 //BADRC EXEC EDIP0022,JOBNAME=EDIBC609,
20	n	EDIBC611
21	n	58 //BADRC EXEC EDIP0022,JOBNAME=EDIBC611,
22	n	EDIBC612
23	n	116 //BADRC EXEC EDIP0022,JOBNAME=EDIBC612,
24	n	EDIBC613
25	n	84 //BADRC EXEC EDIP0022,JOBNAME=EDIBC613,
26	n	EDIBC614
27	n	79 //BADRC EXEC EDIP0022,JOBNAME=EDIBC614,
28	n	EDIBC624
29	n	689 //BADRC EXEC EDIP0022,JOBNAME=EDIBC624,
30	n	EDIBFD42
31	n	35 //BADRC EXEC EDIP0022,JOBNAME=EDIBFD42,
32	n	EDIBW700
33	n	94 //BADRC EXEC EDIP0022,JOBNAME=EDIBW700, * EDIP0022 SETS RC=999
34	n	EDIF0565
35	n	100 //BADRC EXEC EDIP0022,JOBNAME=EDIF0565,
36	n	EDIF1133
37	n	101 //BADRC EXEC EDIP0022,JOBNAME=EDIF1133,
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A:A	Cell Value	="N"	text	NO
A:A	Cell Value	="Y"	text	NO

I want to grab line two and place it in cell C right above the line that has EDI job names like this is what the result should be:

Incidents Project - Jobs Converted-l.xlsm
	A	B	C
6	n	EDIBC601	83 //BADRC EXEC EDIP0022,JOBNAME=EDIBC601,
7	n	EDIBC604	959 //BADRC EXEC EDIP0022,JOBNAME=EDIBC604,
8	n	EDIBC605	64 //BADRC EXEC EDIP0022,JOBNAME=EDIBC605,
9	n	EDIBC606	145 //BADRC EXEC EDIP0022,JOBNAME=EDIBC606,
10	n	EDIBC607	103 //BADRC EXEC EDIP0022,JOBNAME=EDIBC607,
11	n	EDIBC608	357 //BADRC EXEC EDIP0022,JOBNAME=EDIBC608,
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A:A	Cell Value	="N"	text	NO
A:A	Cell Value	="Y"	text	NO

Thats all I need so how can I accomplish that using a Macro? Thanks for any & all Help - Stay Safe EVERYONE!!

 

[JLGWhiz]

This is based on he images in Post #7. But it will not accomodate entries like those on row 33 which vary in format.

Sub t()
Dim i As Long
    With Sheets("Sheet1") 'Edit sheet name
        For i = .Cells(Rows.Count, 2).End(xlUp).Row To 7 Step -1
            If Mid(.Cells(i, 2).Value, InStr(.Cells(i, 2), "=") + 1) = .Cells(i - 1, 2).Value & "," Then
                .Cells(i - 1, 3) = .Cells(i, 2).Value
                .Rows(i).Delete
            End If
        Next
        .Columns("B:C").AutoFit
    End With
End Sub

 

[RudeBoy]

This is based on he images in Post #7. But it will not accomodate entries like those on row 33 which vary in format.

Sub t()
Dim i As Long
    With Sheets("Sheet1") 'Edit sheet name
        For i = .Cells(Rows.Count, 2).End(xlUp).Row To 7 Step -1
            If Mid(.Cells(i, 2).Value, InStr(.Cells(i, 2), "=") + 1) = .Cells(i - 1, 2).Value & "," Then
                .Cells(i - 1, 3) = .Cells(i, 2).Value
                .Rows(i).Delete
            End If
        Next
        .Columns("B:C").AutoFit
    End With
End Sub

Ok I ran that and it ran from row 7 down. Is there away to have it run from Row 1?
Thanks for your help - Take Care & Stay Safe