Stop writing cells at row 50 and wrap the remainder of the text to the next columns

pcalf

New Member
Joined
Sep 26, 2017
Messages
3
I am a novice user of excel, at best, however, my spreadsheet has 85 rows of data in columns A, B, and C. I want to write the rows of data from rows 1-50 in A, B and C and from rows 51-85, I want to write in row 1 and following the remainder of the data in columns D, E and F. Please help!
 

pcalf

New Member
Joined
Sep 26, 2017
Messages
3
forgot to mention, I am looking to write a VBA macro to accomplish this.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,421
Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

When you enter any value in column C from row 51 to 85
The values in column A to C will be entered into column D to F starting in row(1)
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C51:C85")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "D").End(xlUp).Row
If Range("D1").Value = "" Then Lastrow = 0
Target.Offset(1, -2).Select
Range(Cells(Target.Row, 1), Cells(Target.Row, 3)).Cut Range("D" & Lastrow + 1)
End If
End Sub
 
Last edited:

pcalf

New Member
Joined
Sep 26, 2017
Messages
3
Thank you. this did the trick. I might not always have 85 rows, so I tested the if not intersect(target, Range("C51").End(xlDown) is Nothing and seems to be working.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,421
Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.


Thank you. this did the trick. I might not always have 85 rows, so I tested the if not intersect(target, Range("C51").End(xlDown) is Nothing and seems to be working.
 

Forum statistics

Threads
1,085,429
Messages
5,383,614
Members
401,842
Latest member
BathAntelope

Some videos you may like

This Week's Hot Topics

Top