PDA

View Full Version : [SOLVED] Remove lines with no entries and copy back some data



LutonBarry
12-29-2017, 04:46 AM
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
12-30-2017, 10:18 AM
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
12-30-2017, 02:02 PM
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 C9:D9 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
12-30-2017, 04:03 PM
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
12-31-2017, 12:19 PM
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
01-01-2018, 05:03 AM
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.