+ Reply to Thread
Results 1 to 4 of 4

used range ignore first row

  1. #1
    Registered User
    Join Date
    05-20-2020
    Location
    UK
    MS-Off Ver
    Office 16
    Posts
    33

    used range ignore first row

    Hi
    I have some queries and vba. The vba I've cobbled together from posts I've found rather than having any knowledge, so apologies if it looks wrong!
    A query runs (which I've kept simple - it errors 2nd time round if I delete out top row/make as header) and posts data into sheet Get new ORCS1. Get new ORCS2 cell A1=cell A1 in the table created by the query uploaded to Get new ORCS1. The formula is copied down column A.

    The macro is meant to pick up data only not formulae and paste it into the next available cell in ORC Register.

    The problem is Get new ORCS2 is picking up the column header from Get new ORCS1 and hence usedrange is picking up the column header in Get new ORCS2 and pasting that into ORC Register. I know I'm probably missing a simple line of code to exclude the first line from usedrange. Macro is shown below

    Sub RawOtoOsort()
    '
    ' RawOtoOsort Macro
    ' Runs query that takes data from raw orc (as indicated to upload) removes columns, sorts it and removes any duplicates showing the last update only
    '
    ' Keyboard Shortcut: Ctrl+Shift+M
    '
    Windows("UK.xlsm").Activate
    ActiveWorkbook.Save
    ActiveWorkbook.Connections("Query - Raw ORC to New Issue numbers").Refresh


    Dim wb1 As Workbook, sh1 As Worksheet, sh2 As Worksheet
    Set wb1 = Workbooks("UK.xlsm")
    Set sh1 = wb1.Sheets("Get new ORCS2")
    Set sh2 = wb1.Sheets("ORC Register")
    Dim mySeries As Range
    Sheets("Get new ORCS2").Select
    With Range(Cells(1, "A"), Cells(1, "A").End(xlDown))
    .AutoFilter field:=1, Criteria1:="<>" & ""
    On Error Resume Next
    Set mySeries = .SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    If Not mySeries Is Nothing Then
    'update the chart using mySeries
    End If
    End With

    sh1.UsedRange.Copy
    sh2.Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues 'Copies data from source to destination, next avail row.
    wb1.Save
    Sheets("ORC Register").Select
    ActiveWorkbook.Connections("Query - Raw ORC to ORC Sort").Refresh
    Sheets("Raw ORC").Select

    Range("AF3").Copy
    Dim bCell As Range
    Set myRange = Range(Range("Ae3"), Range("Ae3").End(xlDown))
    For Each myCell In myRange
    If myCell.Value <> "" Then
    If bCell Is Nothing Then
    Set bCell = myCell
    Else
    Set bCell = Union(bCell, myCell)
    End If
    End If
    Next
    If Not bCell Is Nothing Then
    bCell.Select
    ActiveSheet.Paste

    End If
    Range("AF3").Select
    Selection.Copy
    Range("AE3:AE20").Select
    ActiveSheet.Paste

    Sheets("ORC Register").Select

    End Sub

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,366

    Re: used range ignore first row

    You could try
    Please Login or Register  to view this content.

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however you need to include code tags around your code.

    Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

    Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    (Note: this change is not optional.)
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    05-20-2020
    Location
    UK
    MS-Off Ver
    Office 16
    Posts
    33

    Re: used range ignore first row

    Wonderful! Thank you, I knew it would be simple but not that simple!

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,366

    Re: used range ignore first row

    You're welcome.


    Please fix the Code Tags as requested or we're both in trouble ... you for not doing it when asked and me for answering the question before you fixed them

+ 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. Replies: 3
    Last Post: 08-12-2019, 02:12 AM
  2. Ignore Subscript Out of Range Error
    By k64 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-30-2017, 04:16 PM
  3. how to ignore N/A in a range
    By bezbid in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-29-2015, 03:52 AM
  4. calculate maximum of a range and ignore if it is 0.
    By tanvi_kalra in forum Excel General
    Replies: 3
    Last Post: 10-30-2014, 04:04 AM
  5. [SOLVED] How do I ignore everything after the last dot in an IP range
    By ajbaldwin in forum Excel General
    Replies: 8
    Last Post: 09-29-2014, 04:08 AM
  6. Find MAX of range - ignore outliers
    By talynthia in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-10-2014, 10:54 AM
  7. Use this range of data, but ignore 0s?
    By ThomasCarter in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 03-11-2013, 12:13 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