PDA

View Full Version : [SOLVED] Modify a macro to remove blanks from a worksheet



khanaran
07-29-2019, 11:36 AM
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
07-29-2019, 12:28 PM
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
07-29-2019, 11:49 PM
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
07-30-2019, 12:39 AM
May I post the file that I made changes as per your guidance

Rajann

Artik
07-30-2019, 01:06 AM
In the attached file you still have ...Find("Race-" & i + 1... instead of ...Find("Race " & i + 1...

Artik

khanaran
07-30-2019, 01:35 AM
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
07-30-2019, 06:30 AM
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
07-30-2019, 06:51 AM
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
07-30-2019, 09:33 AM
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
07-30-2019, 10:11 AM
hi

Many thanx for the help

Its working brilliantly and solved my issue

Kind regards

rajann

khanaran
08-01-2019, 10:31 AM
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
08-01-2019, 01:11 PM
Don't know what the formula's supposed to do.

khanaran
08-01-2019, 01:55 PM
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
08-05-2019, 05:24 AM
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
08-05-2019, 10:29 AM
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