Row data partition - empty column values in a row in one side and non-empties are other side

Question

I am wondering for an VBscript by which i can move the empty row values in one side and the non-empty values in the other side Keeping the data description intact.This can be done using Looping technique. But i want some faster process if any can be implemented using VBscript.

Input Sheet

Code                Error-I                          Error-II                          Error-III



           Type-1    Type-2    Type-3        Test-A      Test-B    Test-C          Prog-A  Prog-B  Prog-C   



Code-A               Yes         No                                  Yes              X              Z     

Code-B                           No                        Yes       Yes                      Y      Z

Code-C              Yes                       Yes                     No                             Z

Output Sheet

Code                Error-I                          Error-II                          Error-III



           Type-1    Type-2                   Test-A      Test-B                    Prog-A  Prog-B   



Code-A        Yes       No                     Yes                                     X     Z     

Code-B        No                               Yes        Yes                          Y     Z

Code-C        Yes                              Yes         No                          Z

Update : After shifting if it is found that a column in a group contains not a single data,that column should need to be dropped form the sheet.

I wrote the below code for all sets of columns but it is producing incorrect data shifts. Can you say where i was wrong?

Option Explicit

Dim objExcel1
Dim strPathExcel1
Dim objSheet1
Dim row,col1,col2
Dim TotlColumnSet : TotlColumnSet =3
Dim AssColmuns : AssColmuns=3
Dim EachColumnSet, ColStart, ColEnd

Set objExcel1 = CreateObject("Excel.Application")
strPathExcel1 = "D:\VA\Copy of Test.xlsx"
objExcel1.Workbooks.open strPathExcel1
Set objSheet1 = objExcel1.ActiveWorkbook.Worksheets(1)

ColStart = 2
For EachColumnSet = 1 To TotlColumnSet

  For row = 3 To 5
    ' find the first empty cell in the row
    col1 = ColStart'2
    ColEnd = ColStart + AssColmuns
    Do Until IsEmpty(objSheet1.Cells(row, col1)) Or col1 > ColEnd-1'4
      col1 = col1 + 1
    Loop

    ' collapse right-hand cells to the left
    If col1 < ColEnd-1 Then '4
      ' proceed only if first empty cell is left of the right-most cell
      ' (otherwise there's nothing to do)
      col2 = col1 + 1
      Do Until col2 > ColEnd-1'4
        ' move content of a non-empty cell to the left-most empty cell, then
        ' increment the index of the left-most empty cell (the cell right of
        ' the former left-most empty cell is now guaranteed to be empty)
        If Not IsEmpty(objSheet1.Cells(row, col2).Value) Then
          objSheet1.Cells(row, col1).Value = objSheet1.Cells(row, col2).Value
          objSheet1.Cells(row, col2).Value = Empty
          col1 = col1 + 1
        End If
        col2 = col2 + 1
      Loop
    End If
  Next

  ColStart = ColEnd

Next

'=======================
objExcel1.ActiveWorkbook.SaveAs strPathExcel1
objExcel1.Workbooks.close
objExcel1.Application.Quit
'======================

Update:

By Mistake i didn't show in the output table columns Type-3,Test-C,Prog-C. But they should need to be present there.

I do not understand what you mean by "move empty **rows** to one side", so what would the above look like when you had run the code? — Fionnuala, Dec 10 '12 at 11:42
@Remou In the Input Sheet above see the data are scattered in Three Groups,Type-I,Type-II and Type-III. But in the in the Output Sheet,you can see all the data are blank and non blank data are partitioned. — Arup Rakshit, Dec 10 '12 at 11:45
I cannot see the point. That makes the headings irrelevant. In the original, Test A has one Yes, in the revised version, it has three. Both cannot be true. — Fionnuala, Dec 10 '12 at 11:49
All are shifted left most if blank cells there. That's the actual requirement.Input sheet has data but they are not organized.But the Output sheet has the organized one. — Arup Rakshit, Dec 10 '12 at 11:52
You have not tagged your question properly. It is possible that you are using VBScript with Excel, but VBA is much more likely. — Fionnuala, Dec 10 '12 at 12:07
You have accepted previous **VBA** answers, are you using VBScript (eg web pages, external to MS Excel) or VBA (written in MS Office code sheets)? — Fionnuala, Dec 10 '12 at 12:15
No, I am using VBScript to code the requirement on Excel.Thus VBA i am not using. — Arup Rakshit, Dec 10 '12 at 12:17
In that case you should add a lot more detail as to what you can and cannot use and why not. It is possible to automate Excel from VBScript and answers are likely to suggest this. Furthermore, unless you have a very good reason for using VBScript, you would be better running the whole thing in Excel, now you have got it there. If it started life as a CSV or text file, then work with that in VBScript. — Fionnuala, Dec 10 '12 at 12:28
You removed the VBA tag, which is strongly followed. If you tag correctly and show some work, you will get answers. I am afraid I am not willing to put the time into this question as it stands. — Fionnuala, Dec 10 '12 at 12:35

Ansgar Wiechers · Accepted Answer · 2012-12-11T22:54:52.270

If I understand you correctly, you want to collapse each column set to the left. If so, the column titles in the result are indeed misleading.

Does the sheet always have 3 rows with 3 sets of 3 columns each? In that case you could simply use the absolute positions of the cells. Example for the first set of columns:

filename = "..."

Set xl = CreateObject("Excel.Application")
xl.Visible = True

Set wb = xl.Workbooks.Open(filename)
Set ws = wb.Sheets(1)

For row = 3 To 5
  ' find the first empty cell in the row
  col1 = 2
  Do Until IsEmpty(ws.Cells(row, col1)) Or col1 > 4
    col1 = col1 + 1
  Loop

  ' collapse right-hand cells to the left
  If col1 < 4 Then
    ' proceed only if first empty cell is left of the right-most cell (otherwise
    ' there's nothing to do)
    col2 = col1 + 1
    Do Until col2 > 4
      ' move content of a non-empty cell to the left-most empty cell, then
      ' increment the index of the left-most empty cell (the cell right of the
      ' former left-most empty cell is now guaranteed to be empty)
      If Not IsEmpty(ws.Cells(row, col2).Value) Then
        ws.Cells(row, col1).Value = ws.Cells(row, col2).Value
        ws.Cells(row, col2).Value = Empty
        col1 = col1 + 1
      End If
      col2 = col2 + 1
    Loop
  End If
Next

nice idea you have provided.But one thing if it is found when all the left shifts are done,that a set having three columns are empty,I want that sets to be deleted in whole. — Arup Rakshit, Dec 11 '12 at 05:22
`col2` must be incremented at the end of the loop, not at the beginning. Fixed. — Ansgar Wiechers, Dec 11 '12 at 22:56
Can you look into the below link which is my another post? -- http://stackoverflow.com/questions/13823912/to-move-the-cell-values-in-a-group-from-right-to-left-if-any-group-of-cells-are — Arup Rakshit, Dec 12 '12 at 02:10
Can you look into my below post? http://stackoverflow.com/questions/13819708/sparse-matrix-creation-using-vbscript#comment19033369_13819708 — Arup Rakshit, Dec 12 '12 at 07:09

Row data partition - empty column values in a row in one side and non-empties are other side

1 Answers1

Linked