VBA to insert row when the date in column c increases in only highlighted selection of data

[David65]

Hi All,

I have data in columns A to K and row 1 has headings. Column C is Payment Date. I have varying numbers of rows each day.

My code so far sorts by column K and then it finds the first instance of text "ZAD -" in column K. It then inserts a row above the first instance of "ZAD -".

It then highlights all data in columns A to K up from this newly inserted row. (so if row 15 is now the blank inserted row, the code will highlight cells K14 to A1)

It then sorts by column C Payment Date. I need to insert a blank row each time the payment date increases in column C of the highlighted cells only.

I have code that inserts a row each time the payment datechanges in column C for the whole document but I need to restrict this commandto just the highlighted data. Remembering that the amount of rows in thehighlighted data will not always be the same.

Anyone know how to do this please?

Thank You

 

[James006]

Hi,

Why dont you post your current macro ... since it requires to be modified with your new condition ...

 

[David65]

Hi James,

The code I tried was this but as you can probably tell it goes to the bottom row of the spreadsheet rather than only starting in my current selection.
[CODESub InsertAtDateChange()
Dim lastRow, chkRw As Integer
lastRow = Range("C" &Rows.Count).End(xlUp).Row
For chkRw = lastRow To 2 Step -1
If Range("C" & chkRw) <>Range("C" & chkRw + 1) Then
Range("C" & chkRw + 1).EntireRow.InsertShift:=xlDown
End If
Next
End Sub
][/CODE]

 

[James006]

Hi,

If my understanding is correct ... you could link your two macros ...

Sub InsertAtDateChange()
Dim lastRow, chkRw As Integer
lastRow = Range("C" & Rows.Count).End(xlUp).Row
  For chkRw = lastRow To 2 Step -1
  If Range("C" & chkRw) <> Range("C" & chkRw + 1) Then
     Range("C" & chkRw + 1).EntireRow.Insert Shift:=xlDown
  End If
  Next chkRw
' Your macro which  highlights your rows
Application.Run ("yoursecondmacro")
End Sub

Hope this will help

 

[David65]

Hi James,

Thank you for your attempt. I think my problem is that the InsertAtDateChange macro looks to checkthe last row of the spreadsheet. I only want it to check from the last row ofmy selection.
So my macro to select the data should run before the insertrow at date change macro.
My spreadsheet might consist of 100 rows. The first say 20for example (this is a moving number) will be highlighted by my current macro.I only want the InsertAtDateChange macro to deal with the rows that arehighlighted and not the ones below.
So today 20 might be highlighted, then a space and then 80 Idon’t want to insert any rows into. Tomorrow it might be 25 are highlighted,then a space and then 50 I don’t want to insert any rows into.
This is my code that highlights the top rows.
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
This should be followed by the InsertAtDateChange code thathopefully only looks at my selected range.
I really appreciate your help. Thanks very much.

 

[James006]

Hi,

Thanks for your explanation ...

In order to only select your highlighted rows ...

You do need to explain HOW these rows get highlighted ...

 

[David65]

Hi James,

This is how the rows get highlight, by the next piece of code. The below code always highlights the correct amount of rows that I want to run the InsertAtDateChange code over. I hope this makes sense.

Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select

 

[James006]

Hi,

Quite honestly ... your request remains quite mysterious ...

Would suggest you post all your macros ... and not just tiny portions ...