Excel VBA - Check each column is empty until last column

[nugab]

Hi everyone, I'm new here. I don't know VBA and have been tasked with a VBA project for whatever reason.
We have a recurring bug wherein a report we receive has semi-fake rows, causing all kinds of hell.

The current logic I am attempting to execute should run as follows:

Loop through each row
    Loop through each column
        If cell "A" though "H" are EMPTY in the current row
            If cell "I" is NOT EMPTY in the current row
                CUT the string in Cell "I", and append it to cell "I" in the current column, in the previous (above) row.

Hoping that, after the script ends, the below data will all be on one row. And the codes in column "I" which have spilt over into other rows, will all be back in column "I".

P/N: XXXXXX BLAHBLAHBLAHBLAH    A1/BA  A1/DG  BK/WH  CB/AN  CB/AU
                                CB/AZ  D1/SD  D2/AH  E2/AA  F1/AG
                                R1/AX  V1/AB  W1/AE

I have been beating my head against a brick wall with this thing for 2 days, and my latest breakthrough has been to delete everything and start again. So I am here asking for help.

Thank you for your time.

[ddanbe]

Hi nugab welcome at Daniweb.
Don't have any idea what you are talking about.
It might help if you could send a picture of the sheet before and after.

[nugab]

Ddanbe, thanks for your feedback. I will try to be more clear.

We get our hands on a weekly report with many thousand rows. For reasons outside of our control, some of the data in column "I" is being cut up and ejected into the next row. Which gives us the following situation:

    P/N: 000001 BLAHBLAHBLAHBLAH    A1/BA  A1/DG  BK/WH  CB/AN  CB/AU
                                    CB/AZ  D1/SD  D2/AH  E2/AA  F1/AG
                                    R1/AX  V1/AB  W1/AE

    P/N: 000002 MOREFFHBLAHBLAH     G1/HA  AH/DD  BD/SH  CX/BN  CB/AX
                                    CH/SZ  AA/HD  Q2/TH  

    P/N: 042654 Thing1              AA/BB HH/GT
    P/N: 062347 Thing2              GL/BM KK/LL PO/IY

    P/N: 377537 SomethingBlar       A1/BA  A1/DG  BK/WH  CB/AN  CB/AU
                                    CB/AZ  D1/SD  D2/AH  E2/AA  F1/AG
                                    R1/AX  V1/AB  W1/AE

I am attempting to find the simplest way of moving the multi-line data back into a single cell. I want each record on a single row.

I want all the Codes in the "XX/XX" format in the same column, even if that column contains 1,000 characters.

        P/N: 000001 BLAHBLAHBLAHBLAH    A1/BA  A1/DG  BK/WH  CB/AN  CB/AU CB/AZ  D1/SD  D2/AH  
        P/N: 000002 MOREFFHBLAHBLAH     G1/HA  AH/DD  BD/SH  CX/BN  CB/AX CH/SZ   
        P/N: 042654 Thing1              AA/BB HH/GT
        P/N: 062347 Thing2              GL/BM KK/LL PO/IY
        P/N: 377537 SomethingBlar       A1/BA  A1/DG  BK/WH  CB/AN  CB/AU CB/AZ  D1/SD  D2/AH  

I am trying to acheieve the following logic.
If cell "A" though "H" are EMPTY in the current row
AND If cell "I" is NOT EMPTY in the current row
CUT the string in Cell "I", and append it to cell "I" in the current column, in the previous (above) row.

We don't have access to the windows 95 machine that spits this report out because the machine is old, highly customized and critical and some people don't want to chance the machine going down by allowing us access.

We cannot use any software package to manipulate the report AFTER we receive it because our government contract disallows us from using ANY software other than their highly modified version of excel. So it has to be an excel script.

This is 6 trillion miles away from what I do.
I hope someone can help.

[nugab]

After re-reading my posts, I am beginning to get the feeling that my inability to explain the problem and my goals, stem from my lack of understanding the problem and my goals.

It's been a long few months.

[overwraith]

I wrote an excel program a while back, If I remember correctly the workspace cell bounds should be uniform regaurdless of whether the cell has data in it or not, or at least that is usually the case. So you should get a well defined box around your data. What I ended up doing is writing basically a wrapper for all the excel stuff, and I got each cell one at a time. This way I could put higher level looping logic in another class which was responsible for everything else. What I was doing was matching two files, not joining cells. In this wrapper though I created a method GetNextCell() which would return the string representation of each cell. I kept the left to right movement and the top to bottom (essentially typewriter like movement) in that method. You could say it is a side effect, but it seemed to work for me.

/*My wrapper basically contained all the excel related elements such as worksheets, excel objects, etc. Was disposable so would close the interop correctly. */

//CtCol, CtRow, and MaxColumns are class level variables
public string GetNextCell(){
    //there are like 3 different options for getting data from excell sheets, text, another for numerics, and another for something else
    var str = XlWorksheet.Cells[ctRow, ctCol].Text;

    //take care of the iteration before we leave the method
    if(CtCol == MaxColumns){
        //excel columns/rows 1 based, not zero
        CtCol = 1;
        CtRow++;
    }else{
        CtCol++;
    }

    return content;
}

Don't have access to the original source, only have a few disperate (out dated) hard copies. Can't recreate without significant time investment.

Using a method like this though I could essentially loop through the excel file a cell at a time without having to worry much about iterating the cell count. You could probably have some pseudo code like the following:

//I like C#
for(int i = 0; i < columnCount; i++){
    //skip or process the preceding columns
    for(int j = 0; j < precedingCols.length; j++){
        //the operation
    }

    //joined columns
    string str = "";
    for(int k = 0; k < numColumnsLeft; k++)
        str += wrapper.GetNextCell();

    //write the resulting string in a single cell in an output file
}

Number of columns left would need calculated, the for loops might not start at 0 since excel is 1 based, but this is just pseudocode.

You should also probably not operate directly on the excel file you are reading, this would be like sorting a list in place or something, the list gets modified so it is harder to do.

[ddanbe]

Do you mean something like this?

[nugab]

Ddanbe, Yes! That is very close to the goal I am aiming for. the only difference being that all the codes in the XX/XX format, should occupy the same cell, instead of one cell for each code.

I made an image for reference, but could not figure out how to upload a picture to this site. I am severely limited in my internet access from my physical location.

If anyone knows the code required to accomplish Ddanbe's image above (including the small caveat I mentioned) I would be eternally grateful.

It doesn't have to be clean or efficient, we are working at a site held together by batch files, automated SSH remote commands, and scotch tape. It's a part of an engineers job across the street to put a piece of tape on a camera lens at 4pm to trigger the low-light detector so that we can have maintenance access to the server room without the alarm going off.

[ddanbe]

The XX / XX format codes of the first row(linr) don't occupy different columns then as I showed in my example?

To upload an image, you just have to click the paperclip button on top of the text input and then browse your computer for an image file.

[nugab]

Ddanbe, your last statement is correct.

The XX/XX codes all belong in the same cell, with one space between each code.

[overwraith]

Stop refering to it as the same cell, you're confusing everybody! A 'cell' is one box. A 'row' is one line of boxes left to right. A 'column' is all the cells in a vertical line (up/down).

So what you seem to want is to take the repeating rows, and put them all on one row, one after the other?

These terms matter when you start dealing with the Excel interop stuff.

[ddanbe]

Do you have the original files(text? DB?) before they are turned into an excel sheet?
Perhaps that would make things less complicated.
An as overwraith pointed out: decide on what you understand is a cell a line a row a column

[nugab]

Overwraith, excellent point. I am only hurting myself when I confuse these terms and will pay more attention. I appreciate your patience.

Ddanbe, sadly what appears in the shared folder overnight is an excel file that looks similar to what I posted above. If it was raw data, comma delimited data, anything else really... I could work with it.

For whatever reason when the XX/XX codes are spit out, every third or fourth word (a space delineated string) the data gets put into the correct Column, but incorrectly the next Row. As you can imagine this causes problems.

[overwraith]

One of the things I have noticed about Excel programs is occasionally Excel thinks that comma separated value text(CSV) files are excel files, so it will automatically open them. You can alter these files with text operations however. Then there are completely different actual excel files which need operated on through excel interop. You are absolutely sure you need to ask Excels permission to modify the files right?

Operating on the assumption that it is in fact an Excel file would this pseudo code be useful?

list ctRow = new list;

Loop through excel file stop when line is empty
    //move next line into the while loop decision part, while readLine != null || not end of file
    list line = ReadLine()//Read line would need implemented return a list of cell content (strings)


    //first cell is blank, is repeating data
    if(String.IsNullOrEmpty(line.elementat(0)))
        ctRow.Append(line)//need to append without the preceding spaces
    else{
        //else is new data, first line is not blank
        ctRow.Write();//write to output excel file
        ctRow.Clear();
    }

end loop

//if we come to the end of the loop, and ctRow still has data we need to write it
if(ctRow.Count > 0)
    ctRow.Write();

If you break up the program into some smaller, more abstract functions you could probably get a more coherent, easily understood program.

I'll try to hack something together today.

[overwraith]

Ok, so I got something working pretty good, I just got to replace this print list function with an actual excel writer which I am going to have to design. Here is my main(it's C#, does it really really have to be VB?)...

/*Author: overwraith*/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Diagnostics;

namespace ExcelOperations {

    class Program {

        static void Main(string[] args) {

            using (ExcelReader reader = new ExcelReader("RefactorThis.xlsx")) {
                List<string> ctRow = reader.ReadLine().ToList();
                string[] line;

                //loop through the excel file
                while (( line = reader.ReadLine() ) != null) {

                    if (line.ElementAt(0) == "") {
                        //first cell is blank, is repeating data

                        //remove preceding spaces
                        line = ( from str in line where !String.IsNullOrEmpty(str) select str ).ToArray();

                        //append the row
                        ctRow.AddRange(line);
                    }
                    else {
                        //else is new data, first line is not blank
                        ctRow.PrintList();
                        ctRow = line.ToList();
                        line = null;
                    }
                }//end loop

                //if we come to the end of the loop, and ctRow still has data we need to write it
                if (ctRow.Count > 0) {
                    ctRow.PrintList();
                }

            }//end usage

            //pauses output only when I am debugging it
            if (Debugger.IsAttached) {
                Console.Write("Press any key to continue... ");
                Console.ReadLine();
            }

        }//end main

    }//end class

}//end namespace

And here is the excel reader that I wrote...

/*Author: overwraith*/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Reflection;//for the Missing enumeration
using System.Runtime.InteropServices;
using Microsoft.Office.Interop.Excel;

namespace ExcelOperations {
    public class ExcelReader : IDisposable {

        private Application XLApp;
        private Workbook XLWorkbook;
        private Worksheet XLWorksheet;

        //Required for the get next cell method
        public int CtRow { get; private set; } //read only
        public int CtCol { get; private set; } //read only

        public int MaxColumns { get; private set; } //read only
        public int MaxRows { get; private set; } //read only

        public string FullPath { get; private set; } //read only
        public string Name { get; private set; } //read only

        public ExcelReader(string fname) {
            if (!File.Exists(fname))
                throw new IOException("Excel file does not exist. ");

            XLApp = new Application();
            XLWorkbook = XLApp.Workbooks.Open(Path.GetFullPath(fname));
            XLWorksheet = XLWorkbook.Sheets[1];
            FullPath = Path.GetFullPath(fname);
            Name = Path.GetFileName(fname);

            //set current row and column
            CtRow = 1;
            CtCol = 1;

            MaxColumns = XLWorksheet.UsedRange.Columns.Count;
            MaxRows = XLWorksheet.UsedRange.Rows.Count;

            //eliminate Excel alerts
            XLApp.DisplayAlerts = false;
        }//end constructor

        //the dispose method for this wrapper class disposes of the excel com objects
        public void Dispose() {
            //Cleanup for the excel data types
            object misValue = Missing.Value;

            //save to same file
            XLWorkbook.Close(true, FullPath, misValue);
            XLApp.Quit();
            releaseObject(XLWorksheet);
            releaseObject(XLWorkbook);
            releaseObject(XLApp);
        }

        //Read Row method, used by ReadAllEntries to read rows from the excel file
        public string[] ReadLine() {
            var rowArr = new string[MaxColumns];

            //return null at end of sheet
            if (CtRow > MaxRows)
                return null;

            for (var col = 1; col <= MaxColumns; col++)
                rowArr[col - 1] = GetNextCell();

            return rowArr;
        } //end method

        //gets one cell from this excel file at a time
        public string GetNextCell() {
            //Reads from left to right, top to bottom

            string content;

            //return null on end of sheet
            if(CtRow <= MaxRows)
                content = XLWorksheet.Cells[CtRow, CtCol].Text;
            else
                return null;

            if (CtCol == MaxColumns) {
                //Have reached end of line
                //when CtCol is > MaxColumns go back to column 1 and increment CtRow
                CtCol = 1;
                CtRow++;
            }
            else {//(CtCol < MaxColumns) just iterate columns until we get to end of line
                CtCol++;
            }

            return content;
        }//end method

        //used for releasing excel com objects
        private void releaseObject(object obj) {
            try {
                Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception) {
                obj = null;
            }
            finally {
                GC.Collect();
            }
        } //end method

    }//end class

}//end namespace

And here is the utility class which I will have to replace with an actual excel writer, but as you can see by running the program, the data is appended correctly.

/*Author: overwraith*/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ExcelOperations {

    public static class CollectionUtil {

        public static void PrintList<T>(this List<T> list){
            for (int i = 0; i < list.Count; i++){
                var ctElement = list.ElementAt(i).ToString();
                Console.Write(i != list.Count - 1 ? ctElement + ", " : ctElement);
            }
            Console.WriteLine();
        }

    }//end class

}//end namespace

[overwraith]

Ok, so I built a writer, here are the code additions...

/*Author: overwraith*/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;//for the Missing enumeration
using System.Runtime.InteropServices;
using Microsoft.Office.Interop.Excel;
using System.IO;

namespace ExcelOperations {

    public class ExcelWriter : IDisposable {

        private Application XLApp;
        private Workbook XLWorkbook;
        private Worksheet XLWorksheet;

        //Required for WriteLine method
        public int CtRow { get; private set; } //read only
        public int CtCol { get; private set; } //read only

        public string FullPath { get; private set; } //read only
        public string Name { get; private set; } //read only

        public ExcelWriter(string fname) {

            XLApp = new Application();
            XLApp.Workbooks.Add(Missing.Value);
            XLWorkbook = XLApp.Workbooks[1];
            XLWorksheet = XLWorkbook.Sheets[1];
            Name = fname;
            FullPath = Path.GetFullPath(fname);

            //set current row and column
            CtRow = 1;
            CtCol = 1;

            //eliminate Excel alerts
            XLApp.DisplayAlerts = false;
        }

        public void WriteLine(object[] value) {
            for (int i = 0; i < value.Length; i++, CtCol++)
                XLWorksheet.Cells[CtRow, CtCol] = value[i];

            CtRow++;
            CtCol = 1;
        }

        //the dispose method for this wrapper class disposes of the excel com objects
        public void Dispose() {
            //Cleanup for the excel data types
            object misValue = Missing.Value;

            //save to same file
            XLWorkbook.SaveAs(FullPath);
            XLWorkbook.Close(true, FullPath, misValue);
            XLApp.Quit();
            releaseObject(XLWorksheet);
            releaseObject(XLWorkbook);
            releaseObject(XLApp);
        }

        //used for releasing excel com objects
        private void releaseObject(object obj) {
            try {
                Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception) {
                obj = null;
            }
            finally {
                GC.Collect();
            }
        } //end method
    }//end class

}//end namespace

And here is the altered main...

/*Author: overwraith*/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Diagnostics;

namespace ExcelOperations {

    class Program {

        static void Main(string[] args) {

            using (ExcelReader reader = new ExcelReader("RefactorThis.xlsx"))
            using (ExcelWriter writer = new ExcelWriter("RefactorThis2.xlsx")) {
                List<string> ctRow = reader.ReadLine().ToList();
                string[] line;

                //loop through the excel file
                while (( line = reader.ReadLine() ) != null) {

                    if (line.ElementAt(0) == "") {
                        //first cell is blank, is repeating data

                        //remove preceding spaces
                        line = ( from str in line where !String.IsNullOrEmpty(str) select str ).ToArray();

                        //append the row
                        ctRow.AddRange(line);
                    }
                    else {
                        //else is new data, first line is not blank
                        //ctRow.PrintList();
                        writer.WriteLine(ctRow.ToArray());
                        ctRow = line.ToList();
                        line = null;
                    }
                }//end loop

                //if we come to the end of the loop, and ctRow still has data we need to write it
                if (ctRow.Count > 0) {
                    //ctRow.PrintList();
                    writer.WriteLine(ctRow.ToArray());
                }

            }//end usage

            //pauses output only when I am debugging it
            if (Debugger.IsAttached) {
                Console.Write("Press any key to continue... ");
                Console.ReadLine();
            }

        }//end main

    }//end class

}//end namespace

I have also seen some systems held together with duckt tape and bailing wire, keeps things interesting doesn't it?

[ddanbe]

This is what I have been trying.
It puts a row underneath the "first" row, next to that row.

Sub MoveRows()

    Const notFound = 0
    Const startCol = 2
    Const ICol = 9

    Dim i As Long
    Dim pos As Integer

    For i = 1 To 2 'Columns(2).Rows.Count 'loop through first column
        pos = InStr(Cells(i, startCol).Value, "P/N") 'test if first cell product
        If pos = notFound Then
            Cells(i, startCol).Select
            Range(Selection.End(xlToRight), Cells(i, Cells(i, Columns.Count).End(xlToLeft).Column)).Select
            Selection.Cut
            Cells(i - 1, ICol).Select 'select cell above in column I
            ActiveSheet.Paste 'paste the selection next to the row above
        Else 'continue
        End If
    Next i
End Sub

This is just a start to get you on the way. Hope it helps.