Tuesday, 22 November 2016

VBA Macro to Insert Rows based on Cell Value in Excel

Excel VBA Macro to Insert Rows based on Cell Value
Sub InsRows()

Dim X As Integer
Dim Y As Integer
Dim Z As Integer
K = 0

For X = 2 To 100

Y = Cells(X, 1).Value
    For Z = 1 To Y
          Cells(X, 1).Offset(1, 0).Rows.Insert
          Cells(X, 1).Offset(1, 0).Interior.ColorIndex = 24    
          K = K + 1
    Next Z

    X = (X + K)
    K = 0
 Next X

End Sub
Suppose we have specified how many rows need to insert a as specified as follows:

Now the above Macro will insert the rows after each cell as shown below, based on number specified.

Monday, 21 November 2016

How to Find when the Table got refreshed last in SQL Server

How to Find when the Table last updated by user in SQL Server
SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,*
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'TAMATAM')     --Database name need to pass here

AND OBJECT_ID=OBJECT_ID('TBL_Weekly_Data') –Table name need to pass here


Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts