+ Reply to Thread
Results 1 to 4 of 4

Exclude header row from dump

Hybrid View

  1. #1
    Registered User
    Join Date
    12-03-2013
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    27

    Question Exclude header row from dump

    Hi there, Ive got the following module running, it checks to see if a value is over 40, then drops all corresponding rows into another sheet. problem being is that is copies some of my header rows, which are just one row at the start of each sheet. anyone suggest how i can get around this dilemma? I am pretty new to this so please be clear with what why how ect. thanks again

    Sub transferdata()
    Dim ws As Worksheet
    Dim rng As Range, cell As Range, rng1 As Range
    Dim lr As Long, lr1 As Long, lrcnt As Long, cnt As Long, m As Long, n As Long
    Application.ScreenUpdating = False
    For Each ws In ActiveWorkbook.Sheets
    If ws.Name <> "Major" Then
    lr = ws.Cells(Rows.Count, "H").End(xlUp).Row
    Set rng = ws.Range("H2:H" & lr)
    n = Application.WorksheetFunction.CountIf(rng, ">40")
    cnt = cnt + n
    End If
    Next ws
    lr1 = Sheets("Major").Cells(Rows.Count, "H").End(xlUp).Row
    Set rng1 = Sheets("Major").Range("H2:H" & lr1)
    m = Application.WorksheetFunction.CountIf(rng1, ">40")
    If m < cnt Then

    For Each ws In ActiveWorkbook.Sheets
    If ws.Name <> "Major" Then
    lr = ws.Cells(Rows.Count, "H").End(xlUp).Row
    Set rng = ws.Range("H2:H" & lr)
    For Each cell In rng
    lr1 = Sheets("major").Cells(Rows.Count, "H").End(xlUp).Row + 1
    If cell.Value > 40 Then
    cell.EntireRow.Copy Destination:=Sheets("Major").Range("A" & lr1)
    End If
    Next cell
    End If
    Next ws
    End If
    Sheets("Major").Activate
    Application.ScreenUpdating = True
    End Sub

  2. #2
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Exclude header row from dump

    Unless I am missing something it should be skipping your header if your header is in row 1 because of the range set:

    Set rng = ws.Range("H2:H" & lr)
    Can you submit your workbook for review?

  3. #3
    Registered User
    Join Date
    12-03-2013
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    27

    Re: Exclude header row from dump

    I cannot submit no sorry

    So of the 12 months that the sheets represent, only 6 heading rows appear, i am assuming that these are the blanks sheets that ahve no data in them.

    Also is there a way to exclude a particular sheet that has values in the h column but is unrelated?

  4. #4
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Exclude header row from dump

    Yes, but without an example copy of your workbook it hard to know how to code it. Best of luck to you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Finging first row of a given column that has data in it.......exclude header
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-14-2011, 02:23 PM
  2. Array Dump
    By Baapi in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-20-2007, 07:03 AM
  3. Replies: 4
    Last Post: 03-21-2006, 02:10 PM
  4. [SOLVED] Exclude Header from Range?
    By nastech in forum Excel General
    Replies: 4
    Last Post: 12-30-2005, 10:55 AM
  5. How to exclude header row from sort button sorts?
    By JCabo in forum Excel General
    Replies: 1
    Last Post: 03-25-2005, 08:06 PM

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