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]

Confirm that the code in in the sheet module and not a standard module.

If that is true then perhaps "events are not enabled". Copy this to the sheet module below the other code and run it.

Sub AppEE()
Application.EnableEvents = True
End Sub

Now re-test the Change_Event code.

If it is still a no-go then you can use one of the link utilities, like drop box, and post a link to your example workbook.
The code works well for me, so not sure why it would fail on your sheet.

Howard

 

[lasusa]

I am still having problems. Link is below:

https://www.dropbox.com/s/jr639t5bwtvao2o/ops notes.xlsm?dl=0

-C

 

[L. Howard]

Confirm that the code in in the sheet module and not a standard module.

Perhaps you are not aware of the difference between the modules as I mention here.

You have the code in Module1 (a standard module) instead of the sheet module for the sheet named Action Items.

I moved the code to the sheet module in your example, and it works well.

Copy the code from Module1 and right click on the sheet tab Action Items > click on View code > paste the code in the large white space. This will be the sheet module.

Return to sheet Action Items and make your entries in column H, does it preform as you want?

Delete the code in the Module 1.

Howard

 

[lasusa]

To quickly answer your question, you are correct - I know NOTHING of VB. I have a mental block with it and have to rely on the kindness of others to help me with simple tasks.

-C

 

[lasusa]

YES! that worked. However, I need to delete the empty row that has been created AND if the data in Column H is deleted I need cells A:H to be moved back up to the active table (no specific place - inserting at Row 10 would be sufficient).

Is it possible?
-C

 

[L. Howard]

Delete the old code and copy this into the sheet module.
This will delete the entire row that has been left blank.

I don't understand this portion of your request???

...AND if the data in Column H is deleted I need cells A:H to be moved back up to the active table (no specific place - inserting at Row 10 would be sufficient).

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
Dim trgRow As Long

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

trgRow = Target.Row

With hRng.Font
  .Strikethrough = True
End With

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

Sheets("Action Items").Rows(trgRow).EntireRow.Delete
End Sub

 

[lasusa]

Thank you so much - this worked!

To your question, as I understand the current script, any time Column H has any change, the script will be enabled to run.

There are occasions where someone will enter a value in Column H and then later determine that they shouldn't have filled in this cell just yet so they will want to delete the contents and move cells A:H back up into the "active" list.

Is it difficult to do this?
-C

 

[L. Howard]

To your question, as I understand the current script, any time Column H has any change, the script will be enabled to run.

There are occasions where someone will enter a value in Column H and then later determine that they shouldn't have filled in this cell just yet so they will want to delete the contents and move cells A:H back up into the "active" list.

Is it difficult to do this?
-C

Not following the inadvertent filled in column H cell scenario.
Do this to help me follow what steps are to occur to recover from a unwanted column H entry.

On the Ops Notes workbook (from the link you posted) clear all data from row 16 on down.
You have data in cells A3 to G15, and column H is blank from H3 on down. (plus rows 1 & 2 as header rows)

In cell A7 enter "Project 7" as a Category and this will be our test row, call it the P7 row.
Now enter "x7" in cell H7.

The script will move P7 row down to row 15 and all entries are struck through. And the data has been moved up to fill the empty row 7.

Now you realize you did not want to move the P7 row... what exactly should happen now?
Where is P7 supposed to go or what is supposed to happen to it?

Howard


Here is some slightly modified code, the change action cells in column H only go as far as down as the data go in column A. It won't work in rows H1 & H2 and will not work for entries below the last row of data in A to H. If you add or delete data the action cells will adjust to the new range of data.

Delete old code and copy this in its place.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("H3", Cells(Rows.Count, "A").End(xlUp))) Is Nothing Then Exit Sub

Dim hRng As Range
Dim trgRow As Long

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

trgRow = Target.Row

With hRng.Font
  .Strikethrough = True
End With

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

Sheets("Action Items").Rows(trgRow).EntireRow.Delete

End Sub

 

[Martin1991]

Hello Howard,
thanks for posting the code, exactly looked for that .Cut keyword somewhere.
May i ask you what means the ByVal keyword defyning the Target?

 

[L. Howard]

Hello Howard,
thanks for posting the code, exactly looked for that .Cut keyword somewhere.
May i ask you what means the ByVal keyword defyning the Target?

Here is a pro's explanation'

Howard

It means that a variable named Target is passed in to the
procedure. The Target variable is a Range type variable, meaning
that it refers to (points to) a cell or range of cells.
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC​