See 2nd highlighted cell in each row If the next cell after 2nd highlighted cell contains data then only clear the row the data and paste 2nd highlighted cell
see the sample file
Conditionally edit row
-
rajakoli636 -
February 23, 2018 at 4:38 PM -
Closed -
Thread is marked as Resolved.
-
-
-
If u have a single doubt plz let me know
-
Do you want the Result to replace the original data on Sheet1 or to be placed elsewhere (on sheet2 for example)?
-
Sorry sir but there is a change
see the result
I have mentioned only the 2nd highlighted cells in the result right
But i want all the data from 2nd highlighted cells till the end of the data in each row means see row 19- here i have mentioned 5 but i want all the data from 2nd highlighted cells means 5,1,42,31,41
and this is final
And i want all the data in sheet 2 -
So you want the data from all Red highlighted cells to now be included in the Result, whereas it was previously excluded?
Will those cells still be highlighted in red?
Also, do you want the values that appear in the Result to be highlighted as they were in the original?
-
-
In this sample file only see the result part ignore rest details in this file
-
I want the result from 2nd highlighted cells till the end of the data exclude all data before 2nd highlighted cells
-
The file Sample2.xlsm.xlsx cannot be opened because "the file format or file extension is not valid".
It would appear that you saved a macro enabled file with a .xlsx extension. Please can you recreate Sample2 and upload again.
-
See this file
-
Try the attached file. Click the button on Sheet1.
Code assigned to that button is:
Code
Display MoreSub EditRows() Dim a, b, c, d, i As Long, ii As Long, iii As Long, iv As Long With Sheets("Sheet1").Cells(1).CurrentRegion a = .Value ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2)) ReDim c(1 To UBound(a, 1), 1 To UBound(a, 2)) ReDim d(1 To UBound(a, 1), 1 To UBound(a, 2)) For i = 1 To UBound(a, 1) For ii = 1 To UBound(a, 2) b(i, ii) = .Cells(i, ii).Interior.Color Next Next End With For i = 1 To UBound(a, 1) c(i, 1) = a(i, 1): d(i, 1) = b(i, 1) Next For i = 1 To UBound(a, 2) c(1, i) = a(1, i): d(1, i) = b(1, i) Next For i = 2 To UBound(a, 1) For ii = 2 To UBound(b, 2) If b(i, ii) = 65535 Then Exit For Next If ii < UBound(a, 2) + 1 Then iii = ii: ii = 2 Do Until ii > UBound(b, 2) If b(i, ii) = 16777215 Then ii = ii + 1: iv = 2 Do c(i, iv) = a(i, ii): d(i, iv) = b(i, ii) ii = ii + 1: iv = iv + 1 Loop Until ii = UBound(a, 2) Exit Do End If ii = ii + 1 Loop Else For ii = 2 To UBound(a, 2) c(i, ii) = a(i, ii): d(i, ii) = 16777215 Next End If Next Application.ScreenUpdating = 0 With Sheets("Sheet2") .Cells(1).CurrentRegion.Clear .[a1].Resize(UBound(c, 1), UBound(c, 2)) = c .Activate Application.Goto .[a1] End With End Sub
-
-
Thnx sir
but kindly stick with the condition
Condition is mentioned in this attachment
Kindly see the attachment we r very close to solution kindly see the attachment i think u will solve this problem perfectly now -
Try this.
-
<<mod edited>>
-
rajakoli636 - Banned. Completely unacceptable response. KJ has responded 5 times to this thread to give you code and free advice on HIS own time. Learn a little gratitude and decorum. Have a nice day.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!