Results 1 to 9 of 9

How to omit rows from Sub parse data VBA

Threaded View

Ninzzii How to omit rows from Sub... 07-29-2016, 10:27 AM
xladept Re: How to omit rows from Sub... 07-29-2016, 10:38 AM
Ninzzii Re: How to omit rows from Sub... 08-03-2016, 05:03 AM
xladept Re: How to omit rows from Sub... 08-03-2016, 09:31 AM
Ninzzii Re: How to omit rows from Sub... 08-08-2016, 06:26 AM
xladept Re: How to omit rows from Sub... 08-08-2016, 09:02 AM
Ninzzii Re: How to omit rows from Sub... 08-08-2016, 11:53 AM
Ninzzii Re: How to omit rows from Sub... 08-08-2016, 10:37 AM
xladept Re: How to omit rows from Sub... 08-08-2016, 01:37 PM
  1. #1
    Registered User
    Join Date
    07-29-2016
    Location
    Bristol, UK
    MS-Off Ver
    2010
    Posts
    7

    How to omit rows from Sub parse data VBA

    Hi

    Please could someone help me modify the below VBA? I am trying to omit the first 6 rows (only 2 are populated) so that when the split into different worksheets occurs, the first 6 rows are not part of the split. I am a macro novice so if you could make it easy to understand, that would be much appreciated. Also, I was trying to use two rows for my header (title from A5:P6), however this didn't seem to work and I have had to resort to using A6:P6, if there is a way of using two rows, please could you let me know?

    Many thanks in advance.

    Sub parse_data()
    Dim lr As Long
    Dim ws As Worksheet
    Dim vcol, i As Integer
    Dim icol As Long
    Dim myarr As Variant
    Dim title As String
    Dim titlerow As Integer
    vcol = 11
    Set ws = Sheets("Cross Co Recharges")
    lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
    title = "A6:P6"
    titlerow = ws.Range(title).Cells(1).Row
    icol = ws.Columns.Count
    ws.Cells(1, icol) = "Unique"
    For i = 2 To lr
    On Error Resume Next
    If ws.Cells(i, vcol) <> "" And Application.WorksheetFunction.Match(ws.Cells(i, vcol), ws.Columns(icol), 0) = 0 Then
    ws.Cells(ws.Rows.Count, icol).End(xlUp).Offset(1) = ws.Cells(i, vcol)
    End If
    Next
    myarr = Application.WorksheetFunction.Transpose(ws.Columns(icol).SpecialCells(xlCellTypeConstants))
    ws.Columns(icol).Clear
    For i = 2 To UBound(myarr)
    ws.Range(title).AutoFilter field:=vcol, Criteria1:=myarr(i) & ""
    If Not Evaluate("=ISREF('" & myarr(i) & "'!A1)") Then
    Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = myarr(i) & ""
    Else
    Sheets(myarr(i) & "").Move after:=Worksheets(Worksheets.Count)
    End If
    ws.Range("A" & titlerow & ":A" & lr).EntireRow.Copy Sheets(myarr(i) & "").Range("A1")
    Sheets(myarr(i) & "").Columns.AutoFit
    Next
    ws.AutoFilterMode = False
    ws.Activate
    End Sub
    Last edited by Ninzzii; 08-03-2016 at 05:02 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. PARSE DATA FROM WEB SITE (with buttons) TO EXCEL (rows, columns).
    By Francisco Sousa in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-18-2015, 02:15 PM
  2. [SOLVED] read pdf or doc file line by line and then parse the data into the proper cells and rows.
    By rtphilli in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 05-04-2014, 03:33 AM
  3. [SOLVED] Parse multiple values in a cell into separate rows
    By bcullers in forum Excel Formulas & Functions
    Replies: 22
    Last Post: 01-25-2013, 03:03 PM
  4. Parse string and move data to multiple rows
    By vbvamsi in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-31-2012, 06:13 PM
  5. Replies: 3
    Last Post: 08-23-2010, 05:07 AM
  6. [SOLVED] how do i omit rows for printing based on column value
    By haloprod in forum Excel General
    Replies: 1
    Last Post: 04-05-2006, 12:10 AM
  7. Replies: 4
    Last Post: 04-20-2005, 06:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1