VBA Programming

Hi,
I am trying to run a VBA macro which would delete the first 6 rows from each tab of an excel (2013) and save the file back with the same name. I am new to VBA and would be grate if you can help me out with a sample code.

Thanks
-H

Hi,
I need to read a list of e.g. Position IDs in a range C2:aa2, in Column A6:A160 is a range of codes with some of these codes relevant to some positions but not all. In the columns below each PositionId the corresponding codes which is relevant to the position is indicated by an “M” or “NM” (only a small number of codes will be relevant to each position, but will be different for each position) I then need to print the resulting data in a table with 3 columns with Column 1 the Code, column 2 the position id and column 3 the “M” or “MN” I have various sheets that I need to compile into one table. Can anyone perhaps assist with an easy solution? Any help or advice will be appreciated.

Great! Thanks for the sample file, here the code:

Sub Atprepare()

lastRow = Sheets(“Data”).Range(“A100000”).End(xlUp).Row
col = 3
jCntr = 4 ‘Print the out from this row
‘Clear Output sheet
tmp = Sheets(“Output”).UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
‘clear output and paste the headers
Sheets(“Output”).Cells.Clear
Sheets(“Home”).Range(“B4:L6”).Copy Destination:=Sheets(“Output”).Range(“A1”)

Do While Trim(Sheets(“Data”).Cells(3, col)) <> ”

For iCntr = 6 To lastRow ‘Here 6 is starting row number of Learning requirement data
‘check if value is M or NM
If Sheets(“Data”).Cells(iCntr, col) = “M” Or Sheets(“Data”).Cells(iCntr, col) = “NM” Then
‘Print the fixed values from Home Sheet’Blue Cells
Sheets(“Output”).Cells(jCntr, 1) = Sheets(“Home”).Cells(7, 2)
Sheets(“Output”).Cells(jCntr, 2) = Sheets(“Home”).Cells(7, 3)

Sheets(“Output”).Cells(jCntr, 4) = Sheets(“Home”).Cells(7, 5)
Sheets(“Output”).Cells(jCntr, 5) = Sheets(“Home”).Cells(7, 6)

Sheets(“Output”).Cells(jCntr, 7) = Sheets(“Home”).Cells(7, 8)
Sheets(“Output”).Cells(jCntr, 8) = Sheets(“Home”).Cells(7, 9)

Sheets(“Output”).Cells(jCntr, 10) = Sheets(“Home”).Cells(7, 11)

‘Now print the remaing values from Data sheet
Sheets(“Output”).Cells(jCntr, 3) = Sheets(“Data”).Cells(iCntr, 1) ‘Learning requirement Number
Sheets(“Output”).Cells(jCntr, 6) = Format(Now(), “dd.mm.yyyy”) ‘Current data
Sheets(“Output”).Cells(jCntr, 9) = Sheets(“Data”).Cells(3, col) ‘Trainee Position code
Sheets(“Output”).Cells(jCntr, 11) = Sheets(“Data”).Cells(iCntr, col) ‘M/NM

jCntr = jCntr + 1
End If
Next

col = col + 1 ‘increase the column to check the next employee

Loop

MsgBox “Done!”
End Sub

Thanks-PNRao!

Hi Ali,
Below is the code you can use Sheet Change event, you can download the example file here .
‘Lock/ unlock a Range of Cells based on Change at a Range and Populate the formulas in Cells
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
‘do if any changes at D5
If Target.Address = Range(“D5”).Address Then

ActiveSheet.Unprotect
Range(“D6:D9”).Locked = False
If Range(“D5”) = “A” Then
‘If A is selected D6 should be cleared And D7, D8 and D9 locked : Formulas D7, =D6*G9, for D8 = D6*G10, for D9 = D6*G11
Range(“D6″) = ”
Range(“D6”).Locked = False

Range(“D7”).Formula = “=D6*G9”
Range(“D7”).Locked = True

Range(“D8”).Formula = “=D6*G10”
Range(“D8”).Locked = True

Range(“D9”).Formula = “=D6*G11”
Range(“D9”).Locked = True

ElseIf Range(“D5”) = “B” Then
‘D7 should become zero or clear-ou
‘Lock and Formulas D6 = D7*G8, for D8 = D6*G10, for D9 = D6*G11
Range(“D7″) = ”
Range(“D7”).Locked = False

Range(“D6”).Formula = “=D7*G8”
Range(“D6”).Locked = True

Range(“D8”).Formula = “=D6*G10”
Range(“D8”).Locked = True

Range(“D9”).Formula = “=D6*G11”
Range(“D9”).Locked = True

ElseIf Range(“D5”) = “C” Then
‘Cell content in D8 and D9 to be zeroed out and available for user input
‘D6 and D7 are locked| D6=(D8+D9)*I9, D7= D6*G9
Range(“D8″) = ”
Range(“D9″) = ”
Range(“D8:D9”).Locked = False

Range(“D6”).Formula = “=(D8+D9)*I9”
Range(“D6”).Locked = True

Range(“D7”).Formula = “=D6*G9”
Range(“D7”).Locked = True

End If
ActiveSheet.Protect
End If

Application.EnableEvents = True

Hope this helps!
Thanks-PNRao!

Hi Vaasu,
Assuming you have populated your data from colum 1 to 30. To hide the column with no data or based on any condition, you can use the below code:

Sub hideColumnsBasedOnCondition()
Dim iCntr As Integer
‘Note: Change the sheet name “YourSheetName” and use this code
For iCntr = 1 To 30

‘check if column is blank
If Application.WorksheetFunction.CountA(Sheets(“YourSheetName”).Columns(iCntr)) = 0 Then ‘if you have column headings then it should be 1, instead of 0
Sheets(“YourSheetName”).Columns(iCntr).Hidden = True
Else
Sheets(“YourSheetName”).Columns(iCntr).Hidden = False
End If

Next

End Sub

Thanks-PNRao!

Hi PNRao,

I would able to do this. Thank you very much for your help on this.

Regards,
Murugan

I have learned a lot from your website about VBA Macro. I am actually doing a project for my final class project and I need to create VBA Macro. I need help on how we can compare two rows and display onto a new worksheet.
I was able to create a macro that displays the fields but I dont know how to display the description because the size of the columns are different and I need to display only the specific cells.

I am not sure how to do that. I believe using 2D array would help me solve this issue but unfortunately I am lost.

If you can please guide me i would greatly appreciate.