Modify a macro to remove blanks from a worksheet

[khanaran]

Hi

I have a macro that was written for me , however the source of my data has now changed and the macro is not doing what I like it to

I will appreciate any help in fixing my macro

Kind regards
Rajann

PS The data is copies into a worksheet called Racescrape and after running the macro it creates a worksheet called Racdata which spaces the data after each race

Now it is not doing that

It creates Racedata but the spacing is not being maintained

[Artik]

Replace line

Set d = .Range("A:A").Find("Race-" & i + 1, LookAt:=xlWhole)

with

Set d = .Range("A:A").Find("Race " & i + 1, LookAt:=xlWhole)

Artik

[khanaran]

Hi Artik

Thank you for your help

However it is not spacing the data between races.

It should have spaces between races in the event there are 1 to twenty horses in the race

Kind regards

rajann

[khanaran]

May I post the file that I made changes as per your guidance

Rajann

[Artik]

In the attached file you still have ...Find("Race-" & i + 1... instead of ...Find("Race " & i + 1...

Artik

[khanaran]

hi Artik

Many thnx for pointing out my error

I fixed the first line and forgot to do like wise in second line

If I may ask

When the macro executes, the 750 from racescrape on line 1 col B moves to col C in Racedata.

second question

will it be possible that
the worksheet analysis be cleared before data is copied from racedata to analysis

regards

Rajann

[p45cal]

try:

Sub MacroTest()
Dim c As Range
Dim d As Range
Dim i As Integer
    
Sheets("Analysis").UsedRange.Clear
    
Application.DisplayAlerts = False
On Error Resume Next
Worksheets("RaceData").Delete
On Error GoTo 0
Application.DisplayAlerts = True
    
Worksheets("Racescrape").Copy Before:=Worksheets(1)
    
With Worksheets(1)
  .Name = "RaceData"
  For i = 1 To 20
    Set c = .Range("A:A").Find("Race " & i, LookAt:=xlWhole)
    c.Offset(1, 0).EntireRow.Delete
    Set d = .Range("A:A").Find("Race " & i + 1, LookAt:=xlWhole)
    If d Is Nothing Then GoTo Finish
    If d.Row - c.Row < 23 Then .Range(d, d.Offset(22 - (d.Row - c.Row))).EntireRow.Insert
    If d.Row - c.Row > 23 Then .Range(d(0), d.Offset(23 - (d.Row - c.Row))).EntireRow.Delete
  Next i
Finish:
  .Range("A:E").EntireColumn.AutoFit
End With
End Sub

[khanaran]

Hi

Many thanx for your help

Yes its working fine for which I an grateful for your help

I created a further sheet and need to fix headings in line , however everytime I run the macro it gets erase.

I also found that when I run the macro its skips the first line and writes from the second line

regards

[p45cal]

try:

Sub MacroTest()
Dim c As Range
Dim d As Range
Dim i As Integer
    
Sheets("Analysis").UsedRange.Offset(1).Clear
    
Application.DisplayAlerts = False
On Error Resume Next
Worksheets("RaceData").Delete
On Error GoTo 0
Application.DisplayAlerts = True
    
Worksheets("Racescrape").Copy Before:=Worksheets(1)
    
With Worksheets(1)
  .Name = "RaceData"
  For i = 1 To 20
    Set c = .Range("A:A").Find("Race " & i, LookAt:=xlWhole)
    c.Offset(1, 0).EntireRow.Delete
    Set d = .Range("A:A").Find("Race " & i + 1, LookAt:=xlWhole)
    If d Is Nothing Then GoTo Finish
    If d.Row - c.Row < 23 Then .Range(d, d.Offset(22 - (d.Row - c.Row))).EntireRow.Insert
    If d.Row - c.Row > 23 Then .Range(d(0), d.Offset(23 - (d.Row - c.Row))).EntireRow.Delete
  Next i
Finish:
  .Range("A:I").EntireColumn.AutoFit
End With
Worksheets("Analysis").Range("a2:b1000").Value = Worksheets("Racedata").Range("a1:b1000").Value
Worksheets("Analysis").Range("c2:f1000").Value = Worksheets("Racedata").Range("d1:g1000").Value
Worksheets("Analysis").Range("g2:h1000").Value = Worksheets("Racedata").Range("h1:i1000").Value
Worksheets("Analysis").Range("i2:j1000").Value = Worksheets("Racedata").Range("ab1:ac1000").Value
Worksheets("Analysis").Range("k2:l1000").Value = Worksheets("Racedata").Range("n1:o1000").Value
Worksheets("Analysis").Range("s2:t1000").Value = Worksheets("Racedata").Range("ah1:ai1000").Value
End Sub

[khanaran]

hi

Many thanx for the help

Its working brilliantly and solved my issue

Kind regards

rajann

[khanaran]

Hi

Many thanx for the help

I tried to add in a formula but its giving me errors

column J form calculator

may you please help

Kind regards

Rajann

[p45cal]

Don't know what the formula's supposed to do.

[khanaran]

Don't know what the formula's supposed to do.

Hi

many thanx for responding

The formula in col J is to find the sumtotal of the value in H( which it reads from bf after it matches H and gets a point value from bg) added to I(which it reads from bi after it matches H and gets a point value from bj) added to I.

It seems to give a error

it may have something to do with the decimal point but was adviced to use the round function so am doing something wrong


Trust this may help in you being able to provide a solution

Kind regards

rajann

[p45cal]

A guess:
Change the cell format of columns BF and BI to 'General'
Change the values in them to round numbers from 0 to 100.
In column J, change instances of ROUND(H72,2) to ROUND(H72,0)
where H72 here is just one example.

Reminder: It's a guess.

[khanaran]

hi

many thanx for your help

I managed to sort the problem out by making a little change with the formula and its working perfectly

I however need a little help with a macro to

sort each race based on a column

kind regards

raj

PS if you would like to see the correction I can upload for you

REgards

Rajann