Delete Rows And Rename Headers

[bcomhari]

Dear All,

I am new to the Excel macros and VBA tools and however i have some design idea how My Tool needs to implemented, We need your support and assistance to get this created, please help here.

Need a help to create macro code by using VBA command Tool.

I have 3 sheets here.

A. Summary Tab (Here I would be uploading a input sheet )
B. Output Tab = required columns to be upload into a server
C. Input Tab = uploaded sheet info will be populated here

Problem Statement:

Input Tab:-> Data alignment command button ( after clicking, it should work as per below)

a) In column “D “ blanks Rows need to be removed
b) Towards to last, I mean in “ BN” column heading name to be added as “Total”
è Column header replacement
1. Business Agreement ID – replaced with Account Number
2. BP ID – replaced with Invoice Number
3. NMI/MIRN/DPI –replaced with NMI
4. Bill Start Date -Period From
5. Bill End Date -Period To
6. Peak Consumption– Peak
7. Shoulder Consumption –Shoulder
8. Off Peak Consumption-OffPeak
9. Capacity Value-Capacity
10. Service Charge-Service
11. Discount Amount($)-Discount
12. Usage and Service Charges-TotalIncGst
13. Total Amount Due -TotalDue
14. In Columns “O & P “ columns we have dates in the format of “YYYYMMDD ‘ and this columns should be replaced with DD/MM/YYYY format
15. Column Name heading –“ BO” name to be updated as “Temp ID” and it should do concatenate " D+A+O" columns

Output Tab: heading names i have already pasted here and now we have 2 command buttons.

-> Command button 1 - Data fetch & Refresh (After clicking, it should fetch the data from the Input data in the below format

a) Each row line item from input data sheet - > should create a 9 rows (times)
b) It should fetches the data based on
c) Based on the heading names in the Output sheet - >fetches the corresponding data from the sheet on below heading

Data should be populated based on heading Index and matching in all 9 columns except( I mean we are repeating row items)

Please help on this

Attached is the output file which has been updated manually , please please on this :crying::crying::banghead::banghead::banghead:

Thanks & Regard,
Hari Prasasd B

[bcomhari]

Can someone please help on this request ? would appreciate your earliest response.

[snb]

Please use a thread title that does reflect the content of your question.

[SamT]

See New Title = Only about two small parts of Project.

a) Deleting Rows:
Set Variable LR to number of last used Row

For i = Lr to 2 Step = -1
IF Cells((i, "D") = "" Then Cells(i, "D").EntireRow.Delete

Column header replacement:
Set Variant Variable to Array("Account Number", "Invoice Number", "Include all headers in Column Number order", "If Blank Use", "")

For ColNum = 1 to Number of columns
Cells(1, ColNum).Value = VariantVariable(ColNum - 1)
Next ColNum

Do this, ask for help here if problem, then post another thread for next part of project

[bcomhari]

Thanks for writing to me, however i am getting below error message " Compile error expected = "

Can you please help me with the detailed code for this . thanks in advance

[SamT]

Show us your code.

In the VBA Express message editor, Click the # icon. This will insert CODE Tags in the message. Then Copy the code in the Excel VBA Editor and Paste it between the CODE Tags

[p45cal]

Cross posted:
http://www.mrexcel.com/forum/excel-questions/889513-visual-basic-applications-macro-code-help.html
http://answertohelp.com/vba-macro-code-help
http://www.msofficeforums.com/excel-programming/28004-vba-macro-code-help.html

bcomhari, worth a read: http://www.excelguru.ca/content.php?184

[bcomhari]

Inputs sheet - > Delete the Empty rows & replace heading names " but unfortunately code is not working .


[CODE]

Sheets("Inputs").Select[/COLOR]
Dim i As Long, Lastrow As Long
Lastrow = Range("D" & Rows.Count).End(xlUp).Row
For i = Lastrow To 1 Step -1
If Cells(i, "D").Value = "" Then
Cells(i, "D").EntireRow.Delete
End If
Next




Sheets("Inputs").Select
Cells.Find(What:="Business Agreement ID", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Cells.Replace What:="Business Agreement ID", Replacement:="Invoice Number", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _



Please help to get this sorted out

[SamT]

Test these two subs. We will put them together after we get them working well.

Note the Dot in front of some terms. This is required when using With Statements. The effect is to place the entire With Parameter in front of the dot. Example From Sub DeleteEmptyRows:
Lastrow = Sheets("Inputs").Columns(4).Cells(Rows.Count).End(xlUp).Row

In the VBA Editor, Right Click "ThisWorkbook" and Insert Module. Put this code in that module.
Option Explicit

Sub DeleteEmptyRows()
Dim i As Long
Dim Lastrow As Long

With Sheets("Inputs").Columns(4)
Lastrow = .Cells(Rows.Count).End(xlUp).Row

'Do we need to check if Row below is truly empty?

For i = Lastrow To 1 Step -1
If .Cells(i).Value = "" Then
.Cells(i).EntireRow.Delete
End If
Next

End With
End Sub


Sub ReplaceHeaders()

Dim OldHeaders As Variant
Dim NewHeaders As Variant
Dim i As Long

OldHeaders = Array("OldHeader1", "OldHeader2", "OldHeader3", Etc)
NewHeaders = Array("NewHeader1", "NewHeader2", "NewHeader3", Etc)

With Sheets("Inputs").Rows(1)
For i = LBound(OldHeaders) To UBound(OldHeaders)
.Replace What:=OldHeaders(i), Replacement:=NewHeaders(i)
Next
End With
End Sub

[bcomhari]

Wow, this is working fine and you are really superb

as you suggested , i have split-ed this project into a 4 modules and created one more module here

http://www.vbaexpress.com/forum/showthread.php?53844-Looping-through-rows-and-copy-cell-values-to-another-worksheet&p=331289#post331289... I would request you to kindly check and help me out Can you please