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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.