• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to Insert Row if a cell has value

jaiprakash

New Member
How to Insert Row if a cell has value,I have attached the Sample Sheet ,where in Need to Make only one pair of segment in each new row
 

Attachments

  • Travel Segments.xlsx
    9.8 KB · Views: 7
Hi p45cal,Thank you very much for taking up my request ,I have attached the update file which shows the desired outcome
 

Attachments

  • Travel Segments.xlsx
    10.2 KB · Views: 5
A couple of buttons in the attached to test and check.
It raises an eyebrow that the World’s #1 Banking Software company should be coming here for help coding.
 

Attachments

  • Chandoo47180Travel Segments.xlsm
    24.4 KB · Views: 6
Hi P45cal,Thank you for the favor very much Appreciated,BTW I am not a Programmer or Tester hence tried to take help here .Besides I believe I am not the first one to seek for help here from a S/W company since Forums Inceptions
 
Hi PASCAL,I need a small Improvisation on earlier file ,If Possible you may Help me
 

Attachments

  • Travel Segments New.xlsx
    10.4 KB · Views: 2
In Sheet1 of the attached, I've removed row 1 where you had Current Format and Desired Format. There's a button at cell N1 which calls this macro:
Code:
Sub blah2()
Dim rngHighLight As Range
Set Destn = Range("O2")
Set myrng = Range("A1").CurrentRegion
Set myrng = Intersect(myrng, myrng.Offset(1))
SceVals = myrng.Value
ReDim Results(1 To myrng.Cells.Count, 1 To 4)
rw = 0
For r = 1 To UBound(SceVals)
  For c = 3 To UBound(SceVals, 2) - 1
    If Len(SceVals(r, c + 1)) > 0 Then
      rw = rw + 1
      Results(rw, 1) = SceVals(r, 1)
      Results(rw, 2) = SceVals(r, 2)
      Results(rw, 3) = SceVals(r, c)
      Results(rw, 4) = SceVals(r, c + 1)
      If c = 3 Then If rngHighLight Is Nothing Then Set rngHighLight = Destn.Offset(rw - 1, 2) Else Set rngHighLight = Union(rngHighLight, Destn.Offset(rw - 1, 2))
    Else
      Exit For
    End If
  Next c
Next r
With Destn.Resize(rw, 4)
  .Value = Results
  .ClearFormats
  .Offset(, 2).Resize(, 2).Font.Color = vbRed    '-16776961
End With
rngHighLight.Font.ColorIndex = xlAutomatic
End Sub

For good measure there's a Power Query solution at cell P3 of the sheet Sheet1 (PQ).
 

Attachments

  • Chandoo47180Travel Segments New.xlsm
    32.8 KB · Views: 5
Back
Top