SOLVED

Refactoring an Excel spreadsheet

%3CLINGO-SUB%20id%3D%22lingo-sub-2749375%22%20slang%3D%22en-US%22%3ERefactoring%20an%20Excel%20spreadsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2749375%22%20slang%3D%22en-US%22%3E%3CP%3EI%20would%20like%20to%20write%20a%20simple%20VBA%20script%20which%20does%20the%20below%2C%20and%20I'd%20really%20like%20some%20help%20filling%20in%20some%20blanks%20which%20I%20don't%20currently%20know.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAssume%20there%20is%20a%20first%20worksheet%20with%20about%2020%20columns%20and%20about%20a%20thousand%20rows.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20column%20i%20in%20range%20of%20column%20B%20to%20S%20(that%20is%2C%20iterating%20through%20all%20columns%20except%20the%20first)%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%20Create%20a%20new%20worksheet%20whose%20name%20is%20the%20header%20(row%201)%20of%20column%20i.%20(For%20example%2C%20if%20column%20B%2C%20row%201%20is%20%22Weather%22%2C%20then%20the%20name%20of%20the%20new%20worksheet%20is%20%22Weather%22.)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%20Copy%20all%20rows%20from%20column%20A%20and%20column%20i%20in%20the%20first%20worksheet%20to%20column%20A%20and%20B%20of%20the%20new%20worksheet.%20(In%20other%20words%2C%20it%20pairs%20the%20first%20column%20with%20each%20column%2C%20from%20the%20original%20worksheet%2C%20and%20sends%20each%20pair%20to%20a%20new%20worksheet.)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%2C%20for%20each%20(new)%20worksheet%20in%20the%20project%2C%20except%20the%20first%2C%20original%20one%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%20For%20each%20row%20in%20the%20sheet%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20If%20the%20column%20B%20cell%20of%20that%20row%20is%20empty%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Delete%20that%20row.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E(In%20other%20words%2C%20go%20through%20all%20of%20the%20new%20sheets%20and%20delete%20any%20rows%20with%20a%20blank%20entry%20in%20the%20second%20column.)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFinally%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20each%20(new)%20sheet%20in%20the%20workbook%2C%20except%20the%20first%2C%20original%20one%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%20For%20each%20row%20in%20the%20sheet%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20If%20the%20cells%20in%20column%20A%20and%20B%20are%20an%20identical%20match%20to%20the%20cells%20in%20column%20A%20and%20B%20but%20in%20some%20other%20row%20-%20i.e.%2C%20an%20exact%20duplicate%20in%20both%20cells%20-%20then%20delete%20that%20row.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%20In%20simpler%20terms%2C%20just%20delete%20any%20duplicate%20values%2C%20but%20it%20has%20to%20be%20a%20match%20in%20both%20columns%20to%20be%20considered%20a%20duplicate.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20my%20attempt%20at%20writing%20this%20in%20code%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDim%20wb%20As%20Workbook%3B%20wb%20%3D%20Workbook(%22test%22)%3C%2FP%3E%3CP%3EDim%20ws1%20As%20Worksheet%3B%20ws1%20%3D%20Workbook.Sheets(%22Sheet1%22)%3C%2FP%3E%3CP%3EDim%20new%20As%20Worksheet%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20column%20i%20in%20ws1.Columns(Range(%22B%22%2C%20%22S%22))%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%20Set%20new%20%3D%20wb.Sheets.Add()%3C%2FP%3E%3CP%3E%26nbsp%3B%20new.Name(column.Range(i%2C%201))%20%23%20attempting%20to%20extract%20name%20from%20cell%20B1%2C%20for%20example%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ecells%20%3D%20ws1.Copy(Column%20A%20and%20i)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Enew.paste(cells)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Efor%20sheet%20in%20Sheets%202%20-%2010%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%20for%20row%20in%20Rows%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20if%20row(column(b)).isBlank()%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20delete(row)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20if%20row.isDuplicate()%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20delete(row)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat's%20the%20basic%20idea.%20I%20know%20the%20code%20is%20very%20vague%2C%20because%20I%20will%20need%20to%20research%20a%20lot%20about%20the%20specific%20syntax%20of%20Visual%20Basic%20for%20this.%20In%20case%20anyone%20can%20fill%20in%20the%20gaps%20in%20my%20understand%2C%20I'd%20hugely%20appreciate%20it.%20Thanks%20very%20much.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2749375%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2749829%22%20slang%3D%22en-US%22%3ERe%3A%20Refactoring%20an%20Excel%20spreadsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2749829%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1154117%22%20target%3D%22_blank%22%3E%40jukhamil%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%20you%20go%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3ESub%20CreateSheets()%0A%20%20%20%20Dim%20wb%20As%20Workbook%0A%20%20%20%20Dim%20w1%20As%20Worksheet%0A%20%20%20%20Dim%20wn%20As%20Worksheet%0A%20%20%20%20Dim%20m%20As%20Long%0A%20%20%20%20Dim%20n%20As%20Long%0A%20%20%20%20Dim%20c%20As%20Long%0A%20%20%20%20Dim%20nm%20As%20String%0A%20%20%20%20Dim%20rng%20As%20Range%0A%20%20%20%20Dim%20crt%20As%20Range%0A%20%20%20%20Application.ScreenUpdating%20%3D%20False%0A%20%20%20%20Set%20wb%20%3D%20ActiveWorkbook%0A%20%20%20%20Set%20w1%20%3D%20wb.Worksheets(1)%0A%20%20%20%20m%20%3D%20w1.Cells.Find(What%3A%3D%22*%22%2C%20SearchOrder%3A%3DxlByRows%2C%20SearchDirection%3A%3DxlPrevious).Row%0A%20%20%20%20n%20%3D%20w1.Cells.Find(What%3A%3D%22*%22%2C%20SearchOrder%3A%3DxlByColumns%2C%20SearchDirection%3A%3DxlPrevious).Column%0A%20%20%20%20Set%20rng%20%3D%20w1.Cells(1%2C%201).Resize(m%2C%20n)%0A%20%20%20%20Set%20crt%20%3D%20w1.Cells(1%2C%20n%20%2B%202).Resize(2)%0A%20%20%20%20w1.Cells(2%2C%20n%20%2B%202).Value%20%3D%20%22%26lt%3B%26gt%3B%22%0A%20%20%20%20For%20c%20%3D%202%20To%20n%0A%20%20%20%20%20%20%20%20Set%20wn%20%3D%20wb.Worksheets.Add(After%3A%3Dwb.Worksheets(wb.Worksheets.Count))%0A%20%20%20%20%20%20%20%20nm%20%3D%20w1.Cells(1%2C%20c).Value%0A%20%20%20%20%20%20%20%20wn.Name%20%3D%20nm%0A%20%20%20%20%20%20%20%20wn.Cells(1%2C%201).Value%20%3D%20w1.Cells(1%2C%201).Value%0A%20%20%20%20%20%20%20%20wn.Cells(1%2C%202).Value%20%3D%20nm%0A%20%20%20%20%20%20%20%20w1.Cells(1%2C%20n%20%2B%202).Value%20%3D%20nm%0A%20%20%20%20%20%20%20%20rng.AdvancedFilter%20Action%3A%3DxlFilterCopy%2C%20CriteriaRange%3A%3Dcrt%2C%20_%0A%20%20%20%20%20%20%20%20%20%20%20%20CopyToRange%3A%3Dwn.Cells(1%2C%201).Resize(1%2C%202)%2C%20Unique%3A%3DTrue%0A%20%20%20%20Next%20c%0A%20%20%20%20crt.ClearContents%0A%20%20%20%20Application.ScreenUpdating%20%3D%20True%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2756821%22%20slang%3D%22en-US%22%3ERe%3A%20Refactoring%20an%20Excel%20spreadsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2756821%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20very%20much%20for%20this%2C%20it%20works%20great.%20If%20you%20don't%20mind%2C%20could%20you%20please%20clarify%20for%20me%20what%20a%20few%20of%20these%20lines%20do%3F%3CBR%20%2F%3E%3CBR%20%2F%3Em%20%3D%20w1.Cells.Find(What%3A%3D%22*%22%2C%20SearchOrder%3A%3DxlByRows%2C%20SearchDirection%3A%3DxlPrevious).Row%3CBR%20%2F%3En%20%3D%20w1.Cells.Find(What%3A%3D%22*%22%2C%20SearchOrder%3A%3DxlByColumns%2C%20SearchDirection%3A%3DxlPrevious).Column%3CBR%20%2F%3EI%20believe%20this%20just%20gets%20the%20dimensions%20of%20the%20Excel%20data.%20Specifically%2C%20the%20asterisk%20means%20%22anything%22%2C%20and%20it%20returns%20the%20index%20of%20the%20last%20row%2Fcolumn.%20Find%20returns%20a%20range%20object%20of%20all%20cells%20matching%20the%20search%20criterion.%20I%20don't%20understand%20what%20xlPrevious%20vs.%20xlNext%20do%20though.%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3ESet%20rng%20%3D%20w1.Cells(1%2C%201).Resize(m%2C%20n)%3CBR%20%2F%3ESet%20crt%20%3D%20w1.Cells(1%2C%20n%20%2B%202).Resize(2)%3CBR%20%2F%3EI%20don't%20understand%20this.%20You%20selected%20the%20first%20cell%2C%20but%20then%20resized%20the%20range%20to%20the%20size%20of%20the%20data.%20And%20what%20is%20crt%20and%20why%20is%202%20being%20added%20to%20n%3F%3CBR%20%2F%3E%3CBR%20%2F%3Ew1.Cells(2%2C%20n%20%2B%202).Value%20%3D%20%22%26lt%3B%26gt%3B%22%3CBR%20%2F%3E%3CBR%20%2F%3EI%20don't%20understand%20why%20you%20entered%20this%20value%20for%20the%20cells.%3CBR%20%2F%3E%3CBR%20%2F%3Ew1.Cells(1%2C%20n%20%2B%202).Value%20%3D%20nm%3CBR%20%2F%3EI%20don't%20understand%20this%20line.%3CBR%20%2F%3E%3CBR%20%2F%3Erng.AdvancedFilter%20Action%3A%3DxlFilterCopy%2C%20CriteriaRange%3A%3Dcrt%2C%20_%3CBR%20%2F%3ECopyToRange%3A%3Dwn.Cells(1%2C%201).Resize(1%2C%202)%2C%20Unique%3A%3DTrue%3CBR%20%2F%3EOr%20this%20one.%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20you%20could%20explain%20some%20of%20these%20lines%20to%20me%20I%20would%20really%20appreciate%20it.%20Thank%20you.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

I would like to write a simple VBA script which does the below, and I'd really like some help filling in some blanks which I don't currently know.

 

Assume there is a first worksheet with about 20 columns and about a thousand rows.

 

For column i in range of column B to S (that is, iterating through all columns except the first):

 

 

  Create a new worksheet whose name is the header (row 1) of column i. (For example, if column B, row 1 is "Weather", then the name of the new worksheet is "Weather".)

 

 

  Copy all rows from column A and column i in the first worksheet to column A and B of the new worksheet. (In other words, it pairs the first column with each column, from the original worksheet, and sends each pair to a new worksheet.)

 

 

Then, for each (new) worksheet in the project, except the first, original one:

 

  For each row in the sheet:

 

    If the column B cell of that row is empty:

 

      Delete that row.

 

 

(In other words, go through all of the new sheets and delete any rows with a blank entry in the second column.)

 

 

Finally,

 

 

For each (new) sheet in the workbook, except the first, original one:

 

  For each row in the sheet:

 

    If the cells in column A and B are an identical match to the cells in column A and B but in some other row - i.e., an exact duplicate in both cells - then delete that row.

 

  In simpler terms, just delete any duplicate values, but it has to be a match in both columns to be considered a duplicate.

 

 

Here is my attempt at writing this in code:

 

Dim wb As Workbook; wb = Workbook("test")

Dim ws1 As Worksheet; ws1 = Workbook.Sheets("Sheet1")

Dim new As Worksheet

 

For column i in ws1.Columns(Range("B", "S")):

  Set new = wb.Sheets.Add()

  new.Name(column.Range(i, 1)) # attempting to extract name from cell B1, for example

 

cells = ws1.Copy(Column A and i)

 

new.paste(cells)

 

for sheet in Sheets 2 - 10:

 

  for row in Rows:

 

    if row(column(b)).isBlank():

      delete(row)

 

    if row.isDuplicate()

      delete(row)

 

 

That's the basic idea. I know the code is very vague, because I will need to research a lot about the specific syntax of Visual Basic for this. In case anyone can fill in the gaps in my understand, I'd hugely appreciate it. Thanks very much.

 

 

 

 

 

 

6 Replies
best response confirmed by jukhamil (Contributor)
Solution

@jukhamil 

Here you go:

Sub CreateSheets()
    Dim wb As Workbook
    Dim w1 As Worksheet
    Dim wn As Worksheet
    Dim m As Long
    Dim n As Long
    Dim c As Long
    Dim nm As String
    Dim rng As Range
    Dim crt As Range
    Application.ScreenUpdating = False
    Set wb = ActiveWorkbook
    Set w1 = wb.Worksheets(1)
    m = w1.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    n = w1.Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    Set rng = w1.Cells(1, 1).Resize(m, n)
    Set crt = w1.Cells(1, n + 2).Resize(2)
    w1.Cells(2, n + 2).Value = "<>"
    For c = 2 To n
        Set wn = wb.Worksheets.Add(After:=wb.Worksheets(wb.Worksheets.Count))
        nm = w1.Cells(1, c).Value
        wn.Name = nm
        wn.Cells(1, 1).Value = w1.Cells(1, 1).Value
        wn.Cells(1, 2).Value = nm
        w1.Cells(1, n + 2).Value = nm
        rng.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=crt, _
            CopyToRange:=wn.Cells(1, 1).Resize(1, 2), Unique:=True
    Next c
    crt.ClearContents
    Application.ScreenUpdating = True
End Sub
Thanks so much, I really appreciate it. I'll study this as well as check its execution. Thank you very much.

Thanks very much for this, it works great. If you don't mind, could you please clarify for me what a few of these lines do?

m = w1.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
n = w1.Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
I believe this just gets the dimensions of the Excel data. Specifically, the asterisk means "anything", and it returns the index of the last row/column. Find returns a range object of all cells matching the search criterion. I don't understand what xlPrevious vs. xlNext do though.


Set rng = w1.Cells(1, 1).Resize(m, n)
Set crt = w1.Cells(1, n + 2).Resize(2)
I don't understand this. You selected the first cell, but then resized the range to the size of the data. And what is crt and why is 2 being added to n?

w1.Cells(2, n + 2).Value = "<>"

I don't understand why you entered this value for the cells.

w1.Cells(1, n + 2).Value = nm
I don't understand this line.

rng.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=crt, _
CopyToRange:=wn.Cells(1, 1).Resize(1, 2), Unique:=True
Or this one.

If you could explain some of these lines to me I would really appreciate it. Thank you.

@jukhamil 

 

m = w1.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

 

This says:

Search all cells for any non-blank content: What:="*"

Do so row by row: SearchOrder:=xlByRows

And start from the bottom upwards: SearchDirection:=xlPrevious

The search stops when it finds a row with at least one non-blank cell, and returns the row number of that row.

 

n = w1.Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

 

This says:

Search all cells for any non-blank content: What:="*"

Do so column by column: SearchOrder:=xlByColumns

And start from the right hand side and work your way to the left: SearchDirection:=xlPrevious

The search stops when it finds a column with at least one non-blank cell, and returns the column number of that column.

 

Set rng = w1.Cells(1, 1).Resize(m, n)

 

This sets rng to the range from Cells(1, 1) = A1 to Cells(m, n) = the cell in the last used row and column, i.e. the data range.

 

Set crt = w1.Cells(1, n + 2).Resize(2)

 

This sets crt to the range starting in row 2, two columns to the right of the data range (i.e. a range guaranteed to be outside the data range), and two rows tall. This will be the criteria range for the Advance Filter operation.

 

We then loop through the columns, from column 2 to n, the last used column of the data range.

 

nm = w1.Cells(1, c).Value

sets nm to the column header of column C. This will become the name of the new sheet, and it will also be used in the criteria range.

 

The top cell of the criteria range is set to this column header, and the bottom cell is set to <> meaning: not empty.

 

rng.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=crt, _
CopyToRange:=wn.Cells(1, 1).Resize(1, 2), Unique:=True

 

This executes the Advanced Filter action from the Data tab of the ribbon, with the option to copy the filtered data to a new range (namely the new sheet), with the criteria range specifying that the column we're looking at should be empty, and with the option to copy unique rows only, so that we won't get duplicates.

 

S0760.png

Thanks very much.


> Set crt = w1.Cells(1, n + 2).Resize(2)

> This sets crt to the range starting in row 2, two columns to the right of the data range (i.e. a range guaranteed to be outside the data range), and two rows tall.

.Cells(1, n+2) - this just selects a single cell in row 1, column n+2, not a rectangular selection of cells, is that correct?
Resize(2) changes the height to 2 rows?

You pass this to the criteria range as a way of saying: do not copy any values that are just a row of two blank entries. But why isn't the crt 1 row by 2 columns, instead of 1 column by 2 rows, as I think you were saying?

Thank you very much.

@jukhamil 

 

> .Cells(1, n+2) - this just selects a single cell in row 1, column n+2, not a rectangular selection of cells, is that correct?
> Resize(2) changes the height to 2 rows?

Both are correct.

 

When applying advanced filter, the criteria range has the field names (column headers) in its first row, and the conditions in the row or rows below. See Excel Advanced Filter Introduction 

In the macro, the first row of crt contains the field name, and the second row contains the condition <>, meaning "not empty".