ExcelSujeet (YouTube Blogger)

Example-1   Sub ArrayInVBA() 'Dynamic array declaration Dim arr() As String Dim i As Integer, j As Integer Dim r As Range, rng As Range i = 0 Set rng = Sheet1.Range("a2", Range("a1000").End(xlUp)) 'Counting elements in a range j = rng.Count j = j - 1 'Resizing Array- Assigning dynamic array a fixed-length. 'We may use Redim Preserve arr(j),  if we plan to increase elements in the array which holds some elements already. ReDim arr(j) For Each r In rng     arr(i) = r     i = i + 1 Next For i = LBound(arr) To UBound(arr)     Debug.Print arr(i) Next End Sub

Example-1   Sub FillBlankCellsFromAbove() Application.ScreenUpdating = False 'Instead of Columns(1), we may use Range() also. With Columns(1) .SpecialCells(xlCellTypeBlanks).Formula = "=R[-1]C" 'Convert formulas into static values. .Value = .Value End With Application.ScreenUpdating = True End Sub Example-2 Sub FillBlankCellsFromAbove2() Columns(1).SpecialCells(xlCellTypeBlanks).Formula = "=R[-1]C" Columns(1).Value = Columns(1).Value End Sub

 Select Case (Decision Making) Example-1 Sub WeekdayTestSelectCase() Select Case Weekday(VBA.Date) Case 2 'Monday MsgBox "Ugghhh - - Back to work.", , "Today is Monday" Case 3 'Tuesday MsgBox "At least it's not Monday anymore!", , "Today is Tuesday" Case 4 'Wednesday MsgBox "Hey, we're halfway through the work week!", , "Today is Wednesday" Case 5 'Thursday MsgBox "Looking forward to the weekend.", , "Today is Thursday" Case 6 'Friday MsgBox "Have a nice weekend!", , "Today is Friday!" Case 1, 7 'Saturday or Sunday MsgBox "Hey, it's currently the weekend!", , "Today is a weekend day!" End Select End Sub Example-2 Sub CurrentQuarter() Select Case Month(VBA.Date) Case 1 To 3: MsgBox "Quarter 1" Case 4 To 6: MsgBox "Quarter 2" Case 7 To 9: MsgBox "Quarter 3" Case 10 To 12: MsgBox "Quarter 4" End ...

Resize Range Range("a1").Resize(4, 5).Select Filter Non-Blank Cells in a Range Set rng=Range("a1").CurrentRegion rng.autofilter Field:=5, Criteria:="<>" or rng.autofilter Field:=col, Criteria:="<>" 'Here col is a variable which holds column number Filter Blank Cells in a Range Set rng=Range("a1").CurrentRegion rng.autofilter Field:=5, Criteria:="=" or rng.autofilter Field:=col, Criteria:="=" 'Here  col  is a variable which holds column number Filter values other than some already known values in a Range Dim var_Arr() as Variant, int_X as Integer Dim rng_R as Range, rng_TitleRange as Range ReDim var_Arr(10000) As Variant         int_X = 1         For Each rng_R In rng_TitleRange                          If rng_R <> "A" And rng_R <> "B" And rng_R <> "C" And rng_R <> "D" And rng_R <> "E" And rng_R <> ...

1) Current Region dim lrow as integer, lcol as integer Last Row lrow=range("a1").currentregion.rows.count Last Column lcol=range("a1").currentregion.columns.count 2) Last Row dim lrow as interger a) lrow=cells.find(what:="*", searchorder:=xlbyrows, searchdirection:=xlprevious).row b) lrow=cells(rows.count,1).end(xlup).row 3) Last Column dim lcol as integer a) lcol=cells.find(what:="*", searchorder:=xlbycolumns, searchdirection:=xlprevious).column b) lcol=cells(1,columns.count).end(xltoleft).column

Have you ever come across the data where the text (characters) and numbers are put together in a single cell. Like Employee Name and Employee Code, Customer Name and Customer Number, etc. If the answer is Yes and you want to learn how to separate them using  formula  then this post is definitely for you. This type of data comes into three flavours: 1) Text and Number (Sujeet123) 2) Number and Text (123Sujeet) 3) Text and Numbers tangled (S1u2j3e4e5t6, 1s2u3j4e5e6t) We can separate the first 2 types using the formula and for 3rd type, we need to write a few lines of code (Macro). Let's start with the First one: 1) Text and Number (Sujeet123) Ex-1 Let's assume that  Cell A2  holds Sujeet123 in Sheet1. we will use the below formula with the Left and Right function to separate the Text and Number . =Min(find({01,2,3,4,5,6,7,8,9},A2&"0123456789")) The above formula will give us the starting position of the first Number. To find the Text Part  put the below...

Have you ever felt the need to find few characters from a string either from the left side or from the right side or few characters from the mid of it? If the answer is Yes , then you should learn about the Left(), Right() and Mid() function. Left() Function: Left function helps you find one or more characters from the start of a string . The syntax of the Left function is as follows: ' =Left(text,[num_chars]) ' It has 2 arguments. The first one is " text ". We pass here the string itself or the reference of the cell which holds the string. The second one is " [num_chars] ". Here we pass digits only for the number of characters we want to find out from the start of the string. This is an optional argument. If we don't pass this argument, the left() function will return only 1 character from the start of the string. Note: Arguments in square brackets '[ ]' are always optional for all functions.  Ex-1 Let's write the below formula in cell A2 ...

Suppose 12.34 is written in Cell A1 on Sheet1 and we want to split it into whole number and decimal number without using the round function. We can do this using Trunc() formula.  Trunc() formula removes the decimal part and gives us only the whole part (Integer Number). For the whole part: Type formula in cell B1 '=Trunc(A1)' (Copy whatever is between quotes) This will give 12 For the decimal part: Type formula in Cell C1 '=A1-Trunc(A1)' (Copy whatever is between quotes) This will give 0.34 . The logic behind this is: A1 has 12.34 , A1-Trunc(A1) contains 12.34 - 12 which gives us 0.34 as a final result.