![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
Microsoft 365 and Office | Excel | For home | Windows
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
@ExcelFunTension,
Hope that I have covered all of you questions. See the edited version of the code at bottom of this post.
Q1) I have a question on strPrompt. At the start we assigned strPrompt to a text.
During validation again we assigned strPrompt to a text.
How does second strPrompt invoke InputBox? Just because it was part of Inputbox earlier?
In COUNTA code, if I enter a invalid number or type a single number it throws an error 1004 no cells were found. The Err.number can be converted to a msg debug. Like I was expecting earlier.
A1) Because of the Do/Loop. When it gets to Loop it goes back to Do and starts over and runs the code to create the InputBox. However, if the input is valid then code Exit Do which means it goes to the command line after Loop.
COUNTA code: I suggest that you go back to using the Find to validate the Input. I have edited the code accordingly and it handles not finding the value.
Q2) Alternative syntax.
A2) Following code works. Ensure that you edit the worksheet name to your worksheet name. It finds the last used row and clears from A4 to last row column R.
Sub ClearSheet()
Dim lngLastRow As Long
With ThisWorkbook.Worksheets("Sheet1") ***'Edit "Sheet1" to your sheet name***
lngLastRow = LastRow(.Cells)
.Range(.Cells(4, "A"), .Cells(lngLastRow, "R")).ClearContents
End With
End Sub
Q3)rngTofind is a range variable a etc
A3) Yes! Range variable can be a single cell or many cells.
Q4) MatchCase ????
A4) MatchCase:= True will only find where alpha characters correctly match the case of the value to be found.
MatchCase:= False will find either upper or lower case alpha character so it does not matter how the user enters the character.
I think that strictly speaking, options is actually the dialog screen where you set the arguments. In the old days we used to refer to parameters then somewhere along the line they became known as arguments so I often refer to them as parameters. (Personally I think of an argument as a discussion, sometimes heated, where people disagree but lets not get too deep into that.)
Q5) I assumed that you must have had a template where the borders had been created over a range larger than likely to be encountered and you wanted to remove the borders below the new data. However, now that assumption appears to be incorrect so I will provide you with some code to create the borders on the new data. I assume now that you will want borders from Row 2, Column R to the bottom of the data.
Q6) Can I write the below code as .Range.Cells(4, "O"), Cells(IngLastRow, "O") = strCity
A6) No!. Syntax must be as I have provided.
The date format is very flexible. Any valid date format that can be used on a worksheet. Note the following examples which you might find interesting.
ddd dd mmm yyyy displays as Mon 08 Mar 2021
dddd dd mmmm yyyy displays as Monday 08 March 2021
Q7) And I noticed we used comma instead of colon e.g O4,O10 = strCity?
The syntax when cells function is used is as per the following example code with the comma.
.Range(.Cells(4, "O"), .Cells(lngLastRow, "O")) = strCity
The colon is used in different syntax like .range("O4:O" & lngLastRow). Note that part of the address in in double quotes and it requires the last row variable to be concatenated. The previous example is much cleaner code.)
Q8) I did not get what is LngLastRow + 1 & ":" & .Rows.Count
A8) lngLastRow is the last row with data and because we do not want to clear the borders off the last row of data we add 1 to it which will be the firast non data row.
Q9) To apply boarders to the used rows. I only to need to edit .Borders.LineStyle = ??? (xlnone replace with ?)
A9) See the modified the code to clear all borders (including borders in column headers) and then re-create the borders from row cell A2 to column R and last used row.
Q10) In rngToFind why find "*", xlFormulas, xIPart, xLPrevious instead of xIValues, xIWhole, xINext.
A10) Yes! Asterisk is a wild card and finds any cell that contains any character/s in the cell. In other words a populated cell; not a blank cell.
Using xlFormulas it will fnd a wild card if the cell is populated irrespective of whether it is a formula or value in the cell.
xlPrevious. Firstly you need to understand that when Find is implemented, by default it commences from the first cell of the range to be searched. However, if the first cell is a match, it does not find it first because of xlNext or xlPrevious is telling it to get the Next or Previous; not the first cell where it starts from. So it initially skips the first cell of the range and goes to the next or previous match depending on the argument setting.
xlNext moves forward through the range and when it gets to the end of the range it loops back to the start of the range. xlPrevious searches backwards and when it gets to the start of the range it moves to the end of the range. However, the search still commences by default from the first cell of the range. Because it goes backwards it goes to the last cell in the range (with Cells used as the range (which is the entire worksheet) it goes to the last cell on the worksheet and works backwards from there.) Therefore if searching for the last cell with data it starts from the first cell and then loops around to end of the range to search.
Q11) Can the below public function LastRow (although its predefined function) be written as follows, if not why do we declare the variable within the variable?
A11) We have to pass the range to be searched to the Function. In the example for your project, we pass the entire worksheet range but in another project, you might only want to find the last used row of a specific column or columns and if you pass a smaller range to it then Find only searches the range that is passed to it. Basically the Fundtion is generic to be used under many different situations and the range to search and the value to be found is passed to the function when it is called.
Q12) Else LastRow = 1 my range starts from A4. A1 to A3 are headers should I change last row = 4?
A12) Again it is a generic function that can be used in different projects. In your project, I assume that you will always have the column headers in row 2 and 3 so if no other data then it will always return 3 (last header row). If this creates a problem then in the main code that called the function you would handle it with a test and adjust the value if necessary. The one is mainly there becaue you cannot have a row zero. If the function returns less than 4 then you need to handle the error because no actual data on the sheet.
Next version of code with borders. Firstly all borders are cleared from the worksheet and then the used range from A2 to P and last used row hav eht borders inserted.
Sub MCR()
Dim wbSource As Workbook
Dim wbOutput As Workbook
Dim wsOutput As Worksheet
Dim wsFull As Worksheet
Dim lstObj As ListObject
Dim strPrompt As String
Dim myInp As Variant
Dim lngLastRow As Long
Dim strCity As String
Dim strRowRef As String
strCity = "New York"
Set wbSource = Workbooks("ML.xlsx") 'Assign workbook to workbook variable
Set wsFull = wbSource.Worksheets("Full-View") 'Assign the worksheet to worksheet variable
Set lstObj = wsFull.ListObjects("Table1") 'Assign the Table (List Object) to a List Object variable
'Setting the variables as per the previous 3 lines of code, lstObj now contains the full
'infomation about the Table including the Workbook name, Worksheet name and Table name
Set wbOutput = Workbooks("MCR Macro.xlsm")
Set wsOutput = wbOutput.Worksheets("Sheet1") 'Edit "Sheet1" to the worksheet name for the output
'By using a variable for the prompt, the prompt can be altered if invalid input by User
strPrompt = "Enter the number" & vbCrLf \_
& "Cancel to exit and terminate processing."
Do
'By using default in following line if User errors in the Inlut then
'User can see what was entered and correct it otherwise it will be blank
myInp = Trim(VBA.Interaction.InputBox(Prompt:=strPrompt, Title:="MCR Macro", Default:=myInp))
If myInp = "" Then 'If user cancels then myInp will be zero length string
MsgBox "User cancelled. Processing terminated." 'Optional. Can delete this line and simply Exit
Exit Sub
End If
'If at least one value found in the column to Filter then there will be some output.
If Validate(lstObj.DataBodyRange.Columns(1), myInp) Then
Exit Do 'Go to past Loop command if Valid Input
Else
'Change Input prompt with message re invalid input and loop back to InputBox
strPrompt = "Invalid entry. Please edit the number" & vbCrLf \_
& "Cancel to exit and terminate processing."
End If
Loop 'If Invalid Input will loop back to Do for User to edit Input.
lstObj.Range.AutoFilter Field:=1, Criteria1:=myInp
'DataBodyRange is range under the column headers and Column 8 is the same as offset 7 columns.
lstObj.DataBodyRange.Columns(8).SpecialCells(xlCellTypeVisible).Copy
wsOutput.Range("A4").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks \_
:=False, Transpose:=False
lstObj.AutoFilter.ShowAllData 'Optional: Cancel filtering on the Table
Application.CutCopyMode = False 'Optional: Clear copy from clipboard
Application.Goto wsOutput.Range("A4") 'Optional: To Activate the worksheet with Output
'Note: With / End With the groups can be nested.
'First With wsOutput,
'Then With .Range(.Cells(2, "A"), .Cells(lngLastRow, "Q"))
'Then With .Borders(xlEdgeLeft)
'Note: With Border repeated for each type of border. eg Left, Top, Bottom, Right, Inside Vertical, Inside Horizontal)
With wsOutput
'Next line: Call the UDF to find the last used row on the worksheet
lngLastRow = LastRow(.Cells) '.Cells is entire range of wsOutput worksheet
.Range(.Cells(4, "O"), .Cells(lngLastRow, "O")) = strCity
.Range(.Cells(4, "P"), .Cells(lngLastRow, "P")) = Date 'Date is the current date in VBA
.Range(.Cells(4, "P"), .Cells(lngLastRow, "P")).NumberFormat = "dd-mmm-yyyy"
.Range(.Cells(4, "Q"), .Cells(lngLastRow, "Q")) = "C"
'Initially remove all borders
.Cells.Borders.LineStyle = xlNone
'Insert borders on the used range
With .Range(.Cells(2, "A"), .Cells(lngLastRow, "R"))
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With .Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With .Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
End With
End With
End Sub
Function LastRow(rng As Range) As Long
'Finds the last used row in a worksheet
Dim rngToFind As Range
With rng
Set rngToFind = .Find(What:="\*", \_
LookIn:=xlFormulas, \_
LookAt:=xlPart, \_
SearchOrder:=xlRows, \_
SearchDirection:=xlPrevious, \_
MatchCase:=False)
End With
If Not rngToFind Is Nothing Then
LastRow = rngToFind.Row
Else
LastRow = 1 'If nothing found then set row one as last row
End If
End Function
Function Validate(rngToSearch As Range, varToFind As Variant) As Boolean
'This function not required when COUNTA function used in lieu
Dim rngToFind As Range
With rngToSearch
Set rngToFind = .Find(What:=varToFind, \_
LookIn:=xlValues, \_
LookAt:=xlWhole, \_
SearchOrder:=xlByRows, \_
SearchDirection:=xlNext, \_
MatchCase:=False)
If Not rngToFind Is Nothing Then
Validate = True
Else
Validate = False
End If
End With
End Function