Friday, September 23, 2016

Troubleshoot:copy non-blank cells to row below based on the value "new" in Column O. Error

copy non-blank cells to row below based on the value "new" in Column O.

I have a spreadsheet with many rows that have the text "new" in column O.
 If there is a row with the text "new" in Column O, I need a macro or trick to copy any non-blank data in the cells of the "new" row to the same column of the row below and then
delete the "new" row.
 Any data in the receiving column can be overwritten.
 I've done a lot of searching and haven't found a solution and I don't know how to code VBS.


Solutions to the Problem copy non-blank cells to row below based on the value "new" in Column O.

Download SmartPCFixer to Fix It (Free)

Do you want to copy the cell with "new" to the line below?
Where the comments are the code is currently set to NOT copy 'new".
If you want it to copy "new" then comment out the line and uncomment the line of code below as per the comments.
(Comment lines appear in green when copied into your VBA editor and are prefixed
with a single quote.
Do not uncomment the instruction lines like  'Following line does NOT copy "new" )
To install the code:
Ensure you have a backup of your workbook in case the code does not do exactly as you require.
You cannot use Undo to reverse changes made by macros.
Alt/F11 to open the VBA editor
Select menu item Insert -> Module
Copy the code into the VBA editor.
Alt/Q to exit the VBA editor
Save as Macro Enabled workbook.
Ensure Macros have been enabled.
See help for how to do this.
Set the option to "Disable all macros with notification"
Feel free to get back to me if any problems or instructions are not clear.
Sub ProcessNew()
    Dim lngRows As Long
    Dim i As Long
    Dim rngRow As Range
    Dim rngCel As Range
    With Sheets("Sheet1")
        lngRows = .Cells(.Rows.Count, "O").End(xlUp).Row
        For i = lngRows To 1 Step -1
            If LCase(.Cells(i, "O")) = "new" Then
                Set rngRow = .Range(.Cells(i, "A"), _
                            .Cells(i, .Columns.Count).End(xlToLeft))
                For Each rngCel In rngRow
                    'Following line does NOT copy "new"
                    If rngCel <> "" And LCase(rngCel) <> "new" Then _
                        rngCel.Offset(1, 0) = rngCel
                    'Following line will copy "new"
                    'If rngCel <> "" Then rngCel.Offset(1, 0) = rngCel
                Next rngCel
                .Cells(i, "O").EntireRow.Delete
            End If
        Next i
    End With
End Sub

In this method we will try to Boot in Safe mode with Networking and see if the error still persists.

  • Search "MSCONFIG" and Open.
  • Click on Boot Tab
  • Tick Safe Boot in Boot Option
  • Select Network.
  • Click Apply and OK.
  • Reboot your System.

Another Safe way to Repair the Problem: copy non-blank cells to row below based on the value "new" in Column O.:

How to Fix copy non-blank cells to row below based on the value "new" in Column O. with SmartPCFixer?

1. Download Error Fixer . Install it on your computer.  Click Scan, and it will perform a scan for your computer. The junk files will be shown in the list.

2. After the scan is done, you can see the errors and problems which need to be repaired.

3. When the Fixing part is done, your computer has been speeded up and the errors have been fixed

Related: How Can You Update & Download NVidia 6100, 6800 GS/XT Display Driver v.260.89 WHQL,Where to Download NVidia GeForce 8800 GT WHQL-certified driver v.196.21,How to Update & Download NVidia GeForce Go 7400 Display Driver v.260.19.12,[Solved] Download NVidia GeForce GTX 460M Driver v.331.82,Download NVidia Quadro Plex Model IV VGA Driver v.304.43 Certified,Way to Download RealTek RTL8100C(L) Driver v.5.01,Way to Download RealTek RTL8100E Drivers v.694,Way to Update & Download RealTek RTL8101L Auto Installation Program v.6.110 driver,Method to Update & Download RealTek RTL8111G PXE and RPL ROM code v.2.58 driver,How to Update & Download RealTek RTL8411B(N) Driver v.10.003,Best Way to Update & Download ASUS A53SV nVidia Graphics Driver v.,Method to Herunterladen ASUS K75VJ Intel Rapid Storage Technology Treiber v.,How Can You Update & Download ASUS CG8580 Intel Chipset Driver v.,Method to Update & Download ASUS K41VD Intel INF Update Driver v.,Way to Update & Download ASUS Pro70T NB Probe v.3.0.0026 driver
Read More: [Solved] Context menu not showing up in Windows 7,Troubleshooting:Corrupted drive notice for failure to backup listed files with 0X8100002F error Error,Fast Solution to Problem: Correct the File Installer in Window 7 64-bit to Install New Programs?,Fast Solution to Problem: Count # of Dates between a Range,Tech Support: Count unique lines on a different worksheet,Closing Internet Explorer causes error,Compare 2 Tables and Calculate the PROFIT Faster,Computer goes to Windows Boot Manager after Start,Connect to one of my network computer remotely when don't allow exception box Marked,computer locking up randomly

No comments:

Post a Comment