VBA code needed to move row if cell has value

[lasusa]

I have been unsuccessful locating code that will move a row of data to the bottom of a list once a cell is populated.

I don't think this is difficult to do but I am struggling.

-C

 

[L. Howard]

What cell is to be populated?
What row is to be moved? The whole row or just certain cells in the row?
Where is the list?
What does the data consist of?

Howard

 

[lasusa]

Column H is the cell that will be populated.
The whole row needs to be moved - the row also needs the text in columns A:H lined thru.
The list is in the same worksheet, specifically titled "Action Items"

I am afraid I don't understand the question about what the data consists of?

Thank you for your quick response!
-C

 

[L. Howard]

The whole row (entire row) is columns A to XFD, 16,000 + columns. (later versions)

If you "move" (copy?) row 6 for instance to the Action Items column, it will evoke an error as not able to do so. The only cell you could copy an entire row to would be a cell in column A.

What column is the "Action Items"?

Could it be that you want, when a cell in column H has text entered into it, a macro will then copy cells from A to H (of that row) to the Action Items column, say that column is M, and to the first unused cell in column M. Then strikethrough the A to H cells that were just copied?

Or are the A to H cells copied and transposed to the Action Items column, where it would make a long single column list in that column?

Howard

Howard

 

[L. Howard]

Going on a bit speculation here, but try this.

Put your data in cells A2:H10.
In cell J1 enter header "Action Items".

Copy this code to the sheet module.

Enter text (or number) in any of column H cells 2 to 10.

The code is an event code which will run for each change of cell contents in column H.
The Action Items list will be in column J2 (and to the right).

Howard

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column <> 8 Then Exit Sub


Dim hRng As Range


Set hRng = Target.Offset(, -7).Resize(1, 8)


With hRng
  .Copy Range("J" & Rows.Count).End(xlUp)(2)
End With


With hRng.Font
 .Strikethrough = True
End With


End Sub

 

[lasusa]

Thank you for the quick responses.

I would like to clarify one thing as it may impact the code that was presented. Data is present in columns A:H. When data is entered into column H, I want the text to be struck through and then cells $A$x:$H$x moved to the first available blank row.

Does this make sense?
Thank you again!
-C

 

[L. Howard]

Data is present in columns A:H. When data is entered into column H, I want the text to be struck through and then cells $A$x:$H$x moved to the first available blank row.

Some more clarification...

Say you enter data in cell H6.
The code strikes through cells A6:H6?
And now does the code take the struck through data in A6:H6 and COPY it to the first blank row? Or is it "moved" to that blank row leaving a blank row 6?
Where exactly is the "first blank row"... is it directly below the existing data, say for instance the A:H data goes down to row 35. Is row 36 the first blank row?
Or are there other blank rows within the A:H data that may be blank, (Like row 6, if it is to be 'moved").

Howard

 

[lasusa]

Some more clarification...

Say you enter data in cell H6.
The code strikes through cells A6:H6?
And now does the code take the struck through data in A6:H6 and COPY it to the first blank row? Or is it "moved" to that blank row leaving a blank row 6?
Where exactly is the "first blank row"... is it directly below the existing data, say for instance the A:H data goes down to row 35. Is row 36 the first blank row?
Or are there other blank rows within the A:H data that may be blank, (Like row 6, if it is to be 'moved").

Howard

To answer your questions above, the data will need to be moved, not copied.
And, yes, the first blank row will be directly below the existing data - there are no blank rows interspersed in the data range.

-C

 

[L. Howard]

Try this, copied to the sheet module.
Then enter your data in H column.
This will, however, create a blank row from whence it is CUT.

Howard

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column <> 8 Then Exit Sub

Dim hRng As Range

Set hRng = Target.Offset(, -7).Resize(1, 8)

With hRng.Font
  .Strikethrough = True
End With

With hRng
  .Cut Range("A" & Rows.Count).End(xlUp)(2)
End With

End Sub

 

[lasusa]

I inserted the code, saved the file as a .xlsm and entered data into Column "H" and nothing happened.

-C