Remove lines with no entries and copy back some data

[LutonBarry]

Hello folks and I hope you have had a great Christmas and may I wish you all a very happy New Year for 2018.

I attach a spreadheet the lines in use may vary so it could be 2 or it could be 40 or so.
The data in cells C to H for each line is completed right to left so column H then G and so on.

What I am wanting to achieve is if a line for example Job 5 in Yellow, has no entries in columns C to H the line is deleted and if for example in lines for Job 2 and Job 9 highlighted in Red, if there is and entry in a cell to the right up to and including Column H and leaving a no fill or blank to the left. The last recorded entry 59 for Job 2 and 53 for job 9 is copied into the cells highlighted in Red.

I seem to be going round in circles so I hope you might ba ble to assist.

[offthelip]

Your requirements are unclear:

What I am wanting to achieve is if a line for example Job 5 in Yellow, has no entries in columns C to H the line is deleted

That is easy to understand , however:

and if for example in lines for Job 2 and Job 9 highlighted in Red, if there is and entry in a cell to the right up to and including Column H and leaving a no fill or blank to the left. The last recorded entry 59 for Job 2 and 53 for job 9 is copied into the cells highlighted in Red.

Is certainly not at all clear to me, what you want!!

[LutonBarry]

Thanks for looking and apologies if I wasn't clear. I've uploaded another file V2.

Take line 2 in the red cells C2:G2 i'l like 59 in cell H2 copied.
Take line 9 in the red Cell F9 I'd like 50 from cell G9 copied. In the Green cells C99 i'd like the 48 from Cell E9 copied.

As I mentioned the number of lines will vary as will the cells left blank.

[offthelip]

Still totally unclear:

Take line 2 in the red cells C2:G2 i'l like 59 in cell H2 copied.

Where to??
and by copied do you mean leave the original cell unchanged?


and what about columns I and J?? do those need to be copied?

Take line 9 in the red Cell F9 I'd like 50 from cell G9 copied. In the Green cells C9 i'd like the 48 from Cell E9 copied.

It is totally unclear what you want done with line 9.

do you still need the blank line on row 5 deleted?

Can I suggest that you produce a spreadsheet that shows how you want it to look after the macro is run.

Writing clear unambiguous requirements is a skill which it might be worth your while looking into, because I think what you are trying to do should be very easy and simple to specify, and also very easy to write a macro to do it. However I can't do it if I don't know exactly what rules to apply to determine what to move or copy. ( I don't even know if I am moving or copying!!)

[LutonBarry]

Hello again I do apologise for the confusion. I've torn a muscle in my back which has meant I cannot sit at a PC for very long at all.

I now attach a before and after spreadsheet and I've also come up with some code that does the trick. I've not tidied it up at all, but would be interested in your take on it as I'm not a regular VBA user and I've more than likely taken a very long and over complicated route to resolve the issue.

Sub Macro1()
'
' Macro1 Macro
'

'
    Application.ScreenUpdating = False
    Sheets("Sheet3").Select
    ActiveSheet.Hyperlinks.Delete
    
    Range("L:L,J:J").Select
    Range("J1").Activate
    Selection.Delete Shift:=xlToLeft
    
    Dim FirstCell As Range, LastCell As Range
    Set LastCell = Cells(Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row, _
    Cells.Find(What:="*", SearchOrder:=xlByColumns, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Column)
    Set FirstCell = Cells(Cells.Find(What:="*", After:=LastCell, SearchOrder:=xlRows, _
    SearchDirection:=xlNext, LookIn:=xlValues).Row, _
    Cells.Find(What:="*", After:=LastCell, SearchOrder:=xlByColumns, _
    SearchDirection:=xlNext, LookIn:=xlValues).Column)
    Range(FirstCell, LastCell).Select
    
    'Cells.Select
    Selection.Replace What:="- ", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("A1").Select
    
    Set LastCell = Cells(Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row, _
    Cells.Find(What:="*", SearchOrder:=xlByColumns, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Column)
    Set FirstCell = Cells(Cells.Find(What:="*", After:=LastCell, SearchOrder:=xlRows, _
    SearchDirection:=xlNext, LookIn:=xlValues).Row, _
    Cells.Find(What:="*", After:=LastCell, SearchOrder:=xlByColumns, _
    SearchDirection:=xlNext, LookIn:=xlValues).Column)
    Range(FirstCell, LastCell).Select
    
    Dim lr As Long, r As Long
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    For r = lr To 1 Step -1
    If Range("C" & r).Value = "" And Range("D" & r).Value = "" And Range("E" & r).Value = "" And Range("F" & r).Value = "" And Range("G" & r).Value = "" Then Rows(r).Delete
    Next r
    
    Columns("I:I").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("C:C").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("D1").Select
    Selection.CurrentRegion.Select
        
    On Error GoTo Hello
    
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.FormulaR1C1 = "=RC[1]"
    ActiveCell.Select
    Selection.CurrentRegion.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.Replace What:="0", Replacement:="", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("D1").Select
    Selection.CurrentRegion.Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Application.CutCopyMode = False
    Selection.FormulaR1C1 = "=RC[-1]"
Hello:
Selection.CurrentRegion.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("J:J").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Columns("C:C").Select
    Selection.Delete Shift:=xlToLeft
    Range("A1").Select
    Application.ScreenUpdating = True
    
End Sub

[offthelip]

I looked at your two spreadsheets and once again I find it impossible to see what you want doing. For example looking at the "After" worksheet in E8 you have -48, but in C8 and D8 you have 48. Where did these values come from?? did you just miss out the minus sign by mistake??
In row 2 the Cell H2 seems to have been copied to C2 to G2 . ie. to the left
However in row 6 the cell in G6 seems to have been copied to the right to H6. What are the rules for copying left or right.

I looked at the code you have posted and it is horrible looking code which is very difficult to see what it is doing, so I thought I would try running it on your "Before" spreadsheet to see what it did and to see if I could simplify it. So I ran it and it did somethign, but it didn't come up with a result anything like your "After" spreadsheet. It actually had a different number of lines it the result. (it has deleted job2)

So I am still unable to help you because your requirements are still a mystery to me. I could have guess but that isn't going to help you get a clear idea in your mind as to what you want to do.