Solved How to populate lastrow and shift cell up in a fixed range?

April 3, 2019 at 13:23:53
Specs: Windows 10
 Private Sub Worksheet_Change(ByVal Target As Range)

 If Not Intersect(Target, Sheet1.Range("B2")) Is Nothing Then
    If Sheet1.Range("B2").Value = "2" then 
' I would like to populate with the today () date this fixed range in sheet2 (C2 to C8) 
' cell by cell by using the lastrow and to shift cells up to allow to carry on to populate 
' the last row
   Sheet2.Range("C2:C8").Value = Date
    end if 
end sub 


See More: How to populate lastrow and shift cell up in a fixed range?

Reply ↓  Report •

✔ Best Answer
April 5, 2019 at 07:41:26
As I suspected...incomplete requirements, which now causes to me to go back and rework my code. Why didn't you tell us your full requirements right from the beginning? We can't read minds.

My previous code does exactly what you asked for and what your examples show. This is the first time that you've mentioned that C2:C8 may not be fully populated.

The following code will fill in C2:C8 until C2:C8 are full. After that, the code will shift the dates up and place the current date in C8.

The code assumes that there will never be a blank row between Dates in C2:C8. The blank rows must be below the last entry. In other words, this is allowed:


       C
1    Date 
2 27/02/2019
3 24/03/2019
4 22/04/2019
5 
6 
7 
8 

but this is not:

       C
1    Date 
2 27/02/2019 
3 24/03/2019
4 22/04/2019
5 
6 20/06/2019
7 23/07/2019
8 26/08/2019

Try this code:

Private Sub Worksheet_Change(ByVal Target As Range)

 If Not Intersect(Target, Sheet1.Range("B2")) Is Nothing Then
  If Sheet1.Range("B2").Value = "2" Then
    
    With Sheets(2)
    
'Determine next empty row in Sheets(2).Range("C1:C8")
       nxtRw = WorksheetFunction.CountA(.Range("C1:C8")) + 1

'If C2:C8 is not full, place current date in next empty row
         If nxtRw < 9 Then
           .Range("C" & nxtRw) = Date
           
'If C2:C8 is full, Shift dates up one row, Place current date in C8
         Else
           .Range("C3:C8").Copy .Range("C2")
           .Range("C8") = Date & "d"
         End If
         
    End With
  End If
 End If
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code



#1
April 4, 2019 at 01:29:42
Thanks a lot for this post. I would like if it is possible to sort it out this piece of code in order to populate this range C2 to C8 by filling the last row and shift up (delete one content cell) in order to carry on with population

Reply ↓  Report •

#2
April 4, 2019 at 11:50:18
It appears that this code has been manually entered into your post. Not only do I see lower case words - which the VBA Editor would have "fixed" - but this code won't even run because it is missing an End If.

I'm guessing (which I hate to do) that your code is supposed to look like this:

Private Sub Worksheet_Change(ByVal Target As Range)

 If Not Intersect(Target, Sheet1.Range("B2")) Is Nothing Then
    If Sheet1.Range("B2").Value = "2" Then
' I would like to populate with the today () date this fixed range in sheet2 (C2 to C8)
' cell by cell by using the lastrow and to shift cells up to allow to carry on to populate
' the last row
       Sheet2.Range("C2:C8").Value = Date
    End If
 End If
End Sub

With that said, I'm confused by what you are asking for.

First you said:

I would like to populate with the today () date this fixed range in sheet2 (C2 to C8) cell by cell

but then you said:

by using the lastrow and to shift cells up to allow to carry on to populate the last row

I have no idea what that means.

If you want to populate a fixed range (C2:C8) how does the "last row" enter into it?

I also don't know what you mean by "to shift cells up to allow to carry on to populate the last row"

Please clarify your requirements.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#3
April 4, 2019 at 13:30:43
Thanks a lot for your reply. My range is limited to a few cells ex: C2:C8. Today I have been working on it. This is the best I came out with:
 
Private Sub Worksheet_Change(ByVal Target As Range)
   
eRow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    
 If Not Intersect(Target, Sheet1.Range("B2")) Is Nothing Then
    If Sheet1.Range("B2").Value = "2" Then
    Sheet2.Cells(eRow, 3).Value = Date
    End If
    End If
End Sub

Sorry for not being so clear in the precedent post. I will try to explain myself better. This piece of code allows me to introduce the date in the sheet2 column 3 ("C"). As I previously stated I would like to keep my range limited from C2 to C8 and to continue to update (or to populate, to insert data) to the last row even though the range is full. To do so I would like to clear or empty the last row content and shifting up the other data entries (old dates) into the range. By doing so even though the range is full it would always possible to update it. I have entered an example of what I would like to do if it possible:
1 Example of full range
             C
1         Date 
2 <b>28/01/2019</b> 
3    27/02/2019
4    24/03/2019
5    22/04/2019
6    25/05/2019
7    20/06/2019
8    23/07/2019

2 Example entry last row date in row 8

             C
1         Date 
2    27/02/2019
3    24/03/2019
4    22/04/2019
5    25/05/2019
6    20/06/2019
7    23/07/2019
8    <b>26/08/2019</b>

I would like to keep the range fixed and finish to populate at row 8 and not to carry on from row 9 on going to enter new dates.
If there is another way to sort it but not in terms of lastrow I would be more than happy to consider it.
Many thanks for helping me to solve out this piece of code.

message edited by TOMMASO


Reply ↓  Report •

Related Solutions

#4
April 5, 2019 at 05:54:46
If all you want to deal with is the C2:C8 range, the following code should work. However, for some reason, I suspect that there is more to this than you have described.

The code copies C3:C8 and pastes it starting in C2. This eliminates the date in C2 and shifts the other dates up one row. The code then places the current date in C8.

There is no need to determine a "last row" if you already know the specific Range that you want to reference.

Private Sub Worksheet_Change(ByVal Target As Range)

 If Not Intersect(Target, Sheet1.Range("B2")) Is Nothing Then
  If Sheet1.Range("B2").Value = "2" Then
  
'Shift dates up one row, Place current date in C8
    With Sheets(2)
      .Range("C3:C8").Copy .Range("C2")
      .Range("C8") = Date
    End With
  
  End If
 End If
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#5
April 5, 2019 at 06:24:02
Thanks for your reply. Regarding your code it works ok. Thanks. As you mentioned my intention is to enter more date until I reach a certain X row, in this case 9. I would like to carry on to introduce new date on my range and keep it going introducing a new date as soon I will reach value 2 in sheet1 B2. This will allow me to have a constant flow. So as you said it is more than copy paste dates.

If row from n. 2 to n. 8 are empty carry on to enter dates cell by cell until all cell until row 9 are full. After that carry on by introducing new date as in the examples stated below:

Example 1
C
1 Date
2 28/01/2019 to be eliminated to allow to enter new date in row 8
3 27/02/2019
4 24/03/2019
5 22/04/2019
6 25/05/2019
7 20/06/2019
8 23/07/2019


2 Example entry last row date in row 8

C
1 Date
2 27/02/2019 To be eliminated to allow to enter a new date in row 8
3 24/03/2019
4 22/04/2019
5 25/05/2019
6 20/06/2019
7 23/07/2019
8 26/08/2019

3 Example
C
1 Date
2 24/03/2019 To be eliminated to allow to enter a new date in row 8
3 22/04/2019
4 25/05/2019
5 20/06/2019
6 23/07/2019
7 26/08/2019
8 29/09/2019

and so on with new date entries. My intention is to carry on to populate until row 9 it is reached and after row 9 it is reached to carry on to entry new dates by shifting up the old dates to allow to have a cell empty to enter in the last row the new date (row 8).

Do you think would it be possible? Many thanks for your help and assistance

message edited by TOMMASO


Reply ↓  Report •

#6
April 5, 2019 at 07:41:26
✔ Best Answer
As I suspected...incomplete requirements, which now causes to me to go back and rework my code. Why didn't you tell us your full requirements right from the beginning? We can't read minds.

My previous code does exactly what you asked for and what your examples show. This is the first time that you've mentioned that C2:C8 may not be fully populated.

The following code will fill in C2:C8 until C2:C8 are full. After that, the code will shift the dates up and place the current date in C8.

The code assumes that there will never be a blank row between Dates in C2:C8. The blank rows must be below the last entry. In other words, this is allowed:


       C
1    Date 
2 27/02/2019
3 24/03/2019
4 22/04/2019
5 
6 
7 
8 

but this is not:

       C
1    Date 
2 27/02/2019 
3 24/03/2019
4 22/04/2019
5 
6 20/06/2019
7 23/07/2019
8 26/08/2019

Try this code:

Private Sub Worksheet_Change(ByVal Target As Range)

 If Not Intersect(Target, Sheet1.Range("B2")) Is Nothing Then
  If Sheet1.Range("B2").Value = "2" Then
    
    With Sheets(2)
    
'Determine next empty row in Sheets(2).Range("C1:C8")
       nxtRw = WorksheetFunction.CountA(.Range("C1:C8")) + 1

'If C2:C8 is not full, place current date in next empty row
         If nxtRw < 9 Then
           .Range("C" & nxtRw) = Date
           
'If C2:C8 is full, Shift dates up one row, Place current date in C8
         Else
           .Range("C3:C8").Copy .Range("C2")
           .Range("C8") = Date & "d"
         End If
         
    End With
  End If
 End If
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#7
April 5, 2019 at 07:58:16
Sorry for not being so clear at the start. This is exactly what I was looking for. Thanks a lot for your code. It works perfectly. This will help me to implement some care plans. Thanks a lot.
Best Regards

message edited by TOMMASO


Reply ↓  Report •

Ask Question