Find last used column in row

July 1, 2010 at 12:47:19
Specs: Windows XP
Hello please could someone assist me with this problem?
My wife and I run an athletics club for children which meets Tuesdays and Thursdays. We have approximately 150 kids that are members. We want to put the subs paid into an excel spreadsheet. I have a macro that will filter the childs name
Sub FilterName()
Dim r As Long 'rows to check
Dim Hiders As Range, Found As Range
Dim returnvalue As String
With ThisWorkbook.Worksheets("Sheet1")
returnvalue = InputBox("Please enter Name." & vbCrLf & "This can be the whole word or first 3 letters" & vbCrLf & "e.g. xxxxxxx", "Name Filter")
Application.ScreenUpdating = False
Call UnfilterName
For r = 7 To ActiveSheet.UsedRange.Rows.Count
Set Found = Range("A" & r).Find(What:=returnvalue, _
After:=Range("A" & r), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
'Range("A" & r).End(xlToRight).Select
If Found Is Nothing Then
If Hiders Is Nothing Then
Set Hiders = Rows(r)
Set Hiders = Union(Hiders, Rows(r))
End If: End If: Next r
Hiders.EntireRow.Hidden = True
Application.ScreenUpdating = True
End With
End Sub

Which works well to isolate the childs name. But and this is the part I need help with. Because a year of Tuesdays and Thursdays is over 80 columns I would if it is possible like to find the last date the child paid subs so we can speed up the whole sub paying process. There could be blanks in the payments. The first payment date starts in column E.
Any help is really appreciated

See More: Find last used column in row

Report •

July 1, 2010 at 14:30:01
One possible code syntax to find the last used column in any given row "r" is:

lastCol = Cells(r, Columns.Count).End(xlToLeft).Column

The next empty one would be:

nextCol = Cells(r, Columns.Count).End(xlToLeft).Column + 1

Does that help?

Report •

July 2, 2010 at 00:58:47
				         TUE	THU	TUE	THU

Surname	First Name Contact Number Month  Joined	29/06/10	01/07/10	06/07/10	08/07/10

I tried to use the pre tags but could not really get the hang of it.
Thank you Derby Dad you are a real gentleman for willing to help me with this. I however can not make the extra line of script work within the macro I posted it still filters the name but also still selects column A.
I know it is cheeky but if I post more information it could help.
So A=Surname B=First name C=Contact number D= Date Joined E=Tue F=Thurs etc
From row E the format is currency. There are no formulas in the sheet. There are 3 macro buttons:- sort,filter name and unfilter.
I can see that both your lines of script should work so possibly it is my error with the positioning of them within the overall macro. I have tried it in a couple of places
MatchCase:=False, SearchFormat:=False)
nextCol = Cells(r, Columns.Count).End(xlToLeft).Column + 1
Application.ScreenUpdating = True
nextCol = Cells(r, Columns.Count).End(xlToLeft).Column + 1
not at the same time you understand.
Both as I have said still filter but select column A.
Thank you for reading this and your help thus far.

Report •

Related Solutions

Ask Question