Section Of Code Modification


Well-known Member
Oct 2, 2006
Hi Guys,

I have the following part of code

It counts rows after the word "Date" is found

the problem i have is that it counts some ugly text after the dates, which is what i dont need

is there a way to enter a blank line after the dates have been counted?

the code is

Set keywordCell = .Cells.Find(What:="Date")
        If Not keywordCell Is Nothing Then
            'Count the rows of Match Stats
            rows = 0
            Do While keywordCell.Offset(rows + 1, 0).Value <> ""
                rows = rows + 1
            If rows > 0 Then
                'Copy the rows and 3 columns of Match Stats from web query sheet to Data sheet
                'using copy/paste special/values only
                .Range(keywordCell.Offset(1, 0), keywordCell.Offset(rows, 2)).Copy
                dataSheet.Range(URLcell.Offset(0, 1).Value).PasteSpecial Paste:=xlPasteValues
                Application.CutCopyMode = False
            End If
            MsgBox "Unable to extract Match Stats for " & URLcell.Value & " because the data returned by the web query " & _
                "doesn't contain the word 'Date' which should mark the start of the Match Stats data"
        End If

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().


Well-known Member
Oct 2, 2006
Hi Andrew,

Maybe "Ugly" is the wrong word

The code (part of a bigger code) counts "Date","Match","Points" from a web page and copies it to another sheet but...

it also copies "Game Stats" and some other text at the bottom of the "Date"


22/08/2009	Arsenal v Portsmouth	0
18/08/2009	Celtic v Arsenal	             8
15/08/2009	Everton v Arsenal	             0
Game Stats		Match Stats
2009/10 Value	£5m	Games Started (sub)
Total points	87	No. of Rated 7+
Ranking (position)	13	Star Man Awards
Ranking (overall)	120	Goals
No. of NotW +8	0	No. of NotW +2
Previous Dream Team Record Summary [2008/09]		
Game Stats		Match Stats
2008/09 Value	£5m	Games Started (sub)
Total points	139	No. of Rated 7+
Ranking (position)	3	Star Man Awards
Ranking (overall)	32	Goals
No. of NotW +8	0	No. of NotW +2

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...